• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

Extract text from middle of a string

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
Louverril
Asked:
Louverril
5 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Is Caption:  part of this ?

mx
0
 
VTKeganCommented:

Dim Array() as String

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

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


This will set Array(0) = sometextvalue and Array(1) = 987
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
How about this:


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


Generic:

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

mx
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
You can use it in
vba: theNumberPart = getNumber(me.txtbox1)
query: select getnumber(somecol), ....

It returns 0 if there is no number
0
 
flakierCommented:
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
 
LouverrilAuthor Commented:
Thanks - all of these are useful.

Sorry to take so long to get back
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now