pulling out part of a text string

[Bob]

I have a text field that could start with number, letters or a hyphen and end
the same way. In the middle is a string of numbers anywhere from 5 to 12
digits long. I need a query to pull those numbers out leaving everything
else. I have looked all over and can't find anything that even gives me a
clue.

 

[LilMorePlease]

Do the string of numbers vary in position? Another words, are there a set
number of characters before the string starts or after they end?

 

[Bob]

Yes they vary in position. Any number of characters could be before and/or
after.

 

[fredg]

I have a text field that could start with number, letters or a hyphen and end
the same way. In the middle is a string of numbers anywhere from 5 to 12
digits long. I need a query to pull those numbers out leaving everything
else. I have looked all over and can't find anything that even gives me a
clue.

You can see your data, we can't.
How are we to know which numbers you want if a number can be at the
beginning and/or at the end. Perhaps a few examples of your data would
help.

 

[LilMorePlease]

I don't know of a quick and easy way to do that based on teh functions I know
of. You could export it to Excel and write a quick macro using the record
macro feature to delete a letter. Then modify the code to remove all of the
letters, & non-numeric characters. The only problem with this is that there
will be extra numbers left over and you won't know to delete the front end
ones or the back end ones....

Wish I could help....

 

[John Vinson]

I have a text field that could start with number, letters or a hyphen and end
the same way. In the middle is a string of numbers anywhere from 5 to 12
digits long. I need a query to pull those numbers out leaving everything
else. I have looked all over and can't find anything that even gives me a
clue.

Could you give an example of the field? How do you know what portion
of the field is to be extracted - the first digit? Might you have
something like

-ABC1DEF1234-

and want 1234 to be the result?

You'll probably need some VBA code to do this, and it wouldn't be all
that hard to write - but we need to know just what you're working
with!

John W. Vinson[MVP]

 

[Bob]

Good points all, and I was not thinking this through and at the same time
trying to identify every possiblility they could throw at me. The only
solution is to provide requirements to the users that nothing proceeds the
claim number I am trying to identify.

so if that is the case and if the first 5 to 12 numbers are the claim number
I wish to isolate and after that is usually a hyphen, but not guaranteed, but
should always be some character other than a number how could I do that?

 

[Albert D. Kallal]

So, your numbers are like:

ldsjflsdjfdslkj23lsjfdslfjdskfd

lsjfsldjf938734987ldskjfdslkjf

ldsjfsdlfjsdlfjsdf55sldjfsldfdskjjdf

In the above last example, we would want to extract the number 55?

My function of

? mToken("abcd123def",2)
gives:123

This "token" function I have breaks up any string into a series of tokens.
Each token is either a set of characters, or number

thus

abc123def

The above is 3 tokens
token 1 = abc
token 2 = 123
token 3 = def

So, my function simply treats each string/number "set" as a separate value.

Assuming your numbers always follow a set of characters, then my function
should work for you....

Here it is:

Public Function mToken(s As Variant, gNum As Integer) As Variant

Dim cTokens As New Collection
Dim intPtr As Integer
Dim str As String
Dim c As String

Dim bolNum As Boolean
Dim bolLastNum As Boolean

If IsNull(s) Then Exit Function
' above test will allow this to be using in sql queries
' since null is often passed


c = Mid(s, 1, 1)
bolLastNum = c Like "[0-9]"

For intPtr = 1 To Len(s)
c = Mid(s, intPtr, 1)
bolNum = c Like "[0-9]"

If bolNum = bolLastNum Then
str = str & c
Else
cTokens.Add str
bolLastNum = bolNum
str = c
End If
Next intPtr
If str <> "" Then
cTokens.Add str
End If

mToken = cTokens(gNum)


End Function

 

[John Vinson]

Good points all, and I was not thinking this through and at the same time
trying to identify every possiblility they could throw at me. The only
solution is to provide requirements to the users that nothing proceeds the
claim number I am trying to identify.

so if that is the case and if the first 5 to 12 numbers are the claim number
I wish to isolate and after that is usually a hyphen, but not guaranteed, but
should always be some character other than a number how could I do that?

I'm confused.

If you have ANY control over the users, can't you insist that the
claim number be stored, alone without any other stuff, in the Claim
Number field?

And if you have NO control over the users, how can any program (or for
that matter any human) identify which arbitrary substring of a
free-format string is actually the claim number?

Again: could you give some REAL EXAMPLES of the kind of data that you
need to handle, with the desired result?

John W. Vinson[MVP]

 

[Bob]

Not that much control John. These are searches our folks do on claim
information and they often have to put something in there to identify to them
what they were searching for, kind of a foot note, but only available in this
field of the vendor.

I could be looking at things like:
11565077-LORI MYER
9000654745-MBF
9000637755 BANKS,J
9000-638968-JKL

Albert's token idea is close, but sometimes I see them inadvertantly place
spaces in the number.

 

[John Vinson]

Not that much control John. These are searches our folks do on claim
information and they often have to put something in there to identify to them
what they were searching for, kind of a foot note, but only available in this
field of the vendor.

I could be looking at things like:
11565077-LORI MYER
9000654745-MBF
9000637755 BANKS,J
9000-638968-JKL

Albert's token idea is close, but sometimes I see them inadvertantly place
spaces in the number.

That helps, some, Bob - but I still don't clearly understand. What is
the desired substring in each of these? Your original post mentioned
leading nonnumerics - which these don't have.

I *THINK* VBA code could be written to extract your claim numbers...
at least most of the time... but I don't know the structure or format
of a claim number (you haven't yet posted that information) nor do I
have a complete picture of the data you're stuck with.

John W. Vinson[MVP]

 

[Albert D. Kallal]

Albert's token idea is close, but sometimes I see them inadvertantly place
spaces in the number.

Then just strip out the spaces before you call my routine...

strNumber = stripSpaces([yourFildName])

strNumber = ntoken(strNumber,1)


however, for a numer like

9000-638968-JKL

Do you want 9000, or do you want all numbers? 9000638968 returned?

Furhter, are some like:

9000-638968-JKL-2343

In the above, do you wnat ALL of the number, or just the first set of
numbers?
or, are there examples liek the above?

as metoend, if you write a simple routine to strip out spaces (and perhaps
hynphnes, then again, my example code should do the trick).

In fact, you might even forget my mtoek, and just pull out all number (as
long as there are no other stray numerbe liek my above last number example).

Public Function OnlyNumbers(myphone As String) As String

Dim i As Integer
Dim mych As String

OnlyNumbers = ""

If IsNull(myphone) = False Then

For i = 1 To Len(myphone)
mych = Mid$(myphone, i, 1)
If InStr("0123456789", mych) > 0 Thenf
OnlyNumbers = OnlyNumbers & mych
End If
Next i
End If


End Function

So, you can still well use the above onlynubmers idea, and/or the nToken
idea. Which of the above approach you use depends on if number ever occur
after like my last example:

9000-638968-JKL-2343

If number never occur after the first set of numbers, the simple onlynumbers
as above will do the trick.

If you need the numbers before the first string (JKL), and *somtimes*
numbers can occur after the JKL text, then I would use the stripspaces +
"-", and perhaps strip out a few others. Then throw the results to the
nToken, and pull out the first number. Easy as pie either way....

 

[Bob]

I appologize for the delay but was pulled away for something else.

Here is what I am trying to get out of the field.

11565077-LORI MYER - End result needs to be 11565077
I am not going to concern myself with any leading Alphanumeric. The
structure of the claim number is all numberic and can be anywhere between 6
and 12 characters.

 

[John Vinson]

I appologize for the delay but was pulled away for something else.

Here is what I am trying to get out of the field.

11565077-LORI MYER - End result needs to be 11565077

These strings are TOO BIG to fit in a Long Integer (limited to 2^31-1
or 2147483647).

The last one is the one that concerns me most. You want the first
hyphen ignored, and in the first two instances you want the first
hyphen to terminate the string. OUCH!

You'll need some VBA code to do this. ASSUMING - and depending on the
rest of your data this assumption may be wrong - that:

- Hyphens (and what other delimiters?) can be ignored
- The field will always start with a number
- The character other than - or a digit terminates the string

you should be able to use this:

Public Function ExtractID(strIn As String) As String
Dim iPos As Integer
Dim strChr As String
ExtractID = ""
For iPos = 1 To Len(strIn) ' step byte by byte
strChr = Mid(strIn, iPos, 1) ' examine each byte
Select Case strChr
Case "0" To "9" ' digit
ExtractID = ExtractID & strChr
Case "-" 'do nothing
Case Else
Exit Function
End Select
Next iPos
End Function


John W. Vinson[MVP]