?
Solved

Extract text from middle of a string

Posted on 2010-11-29
7
Medium Priority
?
412 Views
Last Modified: 2012-05-10
I have a text field on a form which has text in it like this:
Caption: text////994////


I want to be able to split out hte number. Bith the number and the text are of varying lengths. How can I split the number out. I put the //// in so there was a divider.

Thanks Lou
0
Comment
Question by:Louverril
7 Comments
 
LVL 75
ID: 34233123
Is Caption:  part of this ?

mx
0
 
LVL 10

Accepted Solution

by:
VTKegan earned 400 total points
ID: 34233137

Dim Array() as String

YourTextField = "sometextvalue////987////"

Array ()= Split (YourTextField,"////")


This will set Array(0) = sometextvalue and Array(1) = 987
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 total points
ID: 34233145
How about this:


Replace(Mid("Caption: text////994////",Instr(1,"Caption: text////994////","////")+4),"////","")


Generic:

Replace(Mid(YourTextString,Instr(1,YourTextString)+4),"////","")

mx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 800 total points
ID: 34233177
Put this function in a Module

Function getNumber(s As String) As Long
While Len(s) > 1 And Not IsNumeric(Left(s, 1))
s = Mid(s, 2, Len(s))
Wend
getNumber = Val(s)
End Function
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 800 total points
ID: 34233185
You can use it in
vba: theNumberPart = getNumber(me.txtbox1)
query: select getnumber(somecol), ....

It returns 0 if there is no number
0
 
LVL 3

Assisted Solution

by:flakier
flakier earned 400 total points
ID: 34233603
If you have control over the text field I humbly suggest using a more sensible delimiter character such as a tab char or comma.  Also consider the ~= operator and match with a regex like
$number ~= /$input/\(\d+\)$/\1/

Open in new window

(one digit or more into capture group one).  This depends on knowing the other part of the string has no digits.
0
 

Author Closing Comment

by:Louverril
ID: 34282580
Thanks - all of these are useful.

Sorry to take so long to get back
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month4 days, 19 hours left to enroll

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question