Solved

Extract text from middle of a string

Posted on 2010-11-29
7
403 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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Is Caption:  part of this ?

mx
0
 
LVL 10

Accepted Solution

by:
VTKegan earned 100 total points
Comment Utility

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
Comment Utility
How about this:


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


Generic:

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

mx
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now