Solved

Extract text from middle of a string

Posted on 2010-11-29
7
405 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 100 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 200 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 200 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 100 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unable to save new report from old one 9 28
User Level Security 6 38
Exporting Access Tables as CSV 3 23
Access Changing Number to Date with Seperator 5 21
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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