Extract text string from a larger text string

[bbull]

Hi everybody,

I am working on a problem for which I have been unable to find a good answer out on the web. I would like to pull nine-digit strings from a string of text that is not uniform in size, content, or data arrangement. The only constant is that the string is always nine digits long. The string can be numeric or alphanumeric as shown below.

My data is as follows:

Text String	Desired Result
Req=6.0002 Ford_ 1055 BORADE STREET, 800929712 22A201 345370860*	345370860
1200 12th Avenue, 815009780_Amazon 023135106 000000000222555561	023135106
ATTN Rich Kelly PMT 1045612957, Amazon** New York, New York CUSIP 023135AK2	023135AK2
ACCT:GOOGLE, ATTN Matt Johnson, 5159357660*38259PAB8 Google Mountain View_141	38259PAB8

<tbody>
</tbody>

There may not be an exact way to do this given the differences in the way the text string data is presented and would even be interested in a partial solution if possible.

Any help offered would be greatly appreciated!

 

[Maverick27]

Sorry, i'm still learning how to write macros, so cant offer a solution.

Judging by the data, there can be multiple sets of 9 Digit Nos in any given Text String, so I guess, you could write a macro to identify ALL, separate, 9 digit Nos in a string & output the desired result into different cols.

 

[Puertorekinsam]

You can try this, however it's not perfect. In your first example, you have multiple 9 digit values. (The one you want ends with a * which technically makes it 10 digits... I have put some error handling in place, but it depends on what you want it to do)

Function NineDigitStirng(TheString As String)


TheString = Replace(TheString, ",", " ")
TheString = Replace(TheString, "*", " ")
i = Split(TheString, " ")

StringCount = 0

For j = 0 To UBound(i)
If Len(i(j)) = 9 Then
    Output = i(j)
    StringCount = StringCount + 1
End If
Next
Select Case StringCount

Case 0
    NineDigitStirng = "#NoResults"
Case 1
    NineDigitStirng = Output
Case Else
    NineDigitStirng = "#MultiResults"
End Select



End Function

 

[István Hirsch]

Referring to the first string, how do you know that 345370860 is the number to be extracted?