Solved

Extract text from middle of a string

Posted on 2010-11-29
7
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

733 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