Extract a 5 Digit Number From Inconsistent Text Strings

[jeffgibson55]

I know how to extract a number from a text string using:

LOOKUP(99^99,--("0"&MID(J1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},J1&"0123456789")),ROW($1:$10000))))

But in my current data set I have thousands of inconsistent text strings some of which contain other numbers that I don't care about - with the above formula it just grabs the first number it finds left to right. The number I need to grab is always a 5 digit number. Is there any formula I can use that would only grab out a 5 digit number ignoring all other numbers? Here's an example text string: "9/14/2010.NTL.TeleBroc.55129T_V1_N" where 55129 is the desired extract value but like I said the other text strings don't necessarily follow this example's format/delimiters.

Thanks!
Jeff

 

[jeffgibson55]

I love this formula solution - works perfectly! Thanks again!

Jeff

 

[dmacmitchell]

PGC,

An excellent formula! (I like to think if given days to work on just this problem, I might have come up with same)

I had the exact same question, and was thrilled to find this thread.

I love this message board. I might buy a Bill Jelen book just out of gratitude.

Dave

 

[pgc01]

Michael, thanks. This is also why I like this board, we learn from each other.

Jeff, Dave, I'm glad it helped. Cheers!

 

[Rick Rothstein]

@Michael M,

Here is a non-RegEx function (UDF) for you to consider... I used D2 to hold the number of digits (just like pgc did in his modification to his UDF)...

Function GetFive(S As String) As Variant
  Dim X As Long, NumOfDigits As Long
  Application.Volatile
  NumOfDigits = Range("D2").Value
  For X = 1 To Len(S) - NumOfDigits + 1
    If Mid(" " & S & " ", X, NumOfDigits + 2) Like "[!0-9]" & String(NumOfDigits, "#") & "[!0-9]" Then
      GetFive = CLng(Mid(S, X, NumOfDigits))
      Exit Function
    End If
  Next
  GetFive = ""
End Function

Since this function uses nothing but built-in VB function calls, I thought you might like to try and work through how it works on your own. If not, or if you become stuck, feel free to ask how all or part of it works. The one part that may be new to you is the Like Operator. This is like a baby RegEx parser... no where near as powerful as a full RegEx engine, but still capable of doing some quite strong parsing. The help files for the Like Operator should give you all the information you need about it. Also note that I set the function to return the empty string ("") instead of 0 in case there are no 5-digit numbers in the text.

 

[pgc01]

Hi Rick

Why did you make the udf volatile? In makes it less efficient and I don't see the need for it.

 

[Rick Rothstein]

Why did you make the udf volatile? In makes it less efficient and I don't see the need for it.

When you change the value in D2 (the number of digits to retrieve), the formula result does not update unless the function is made volatile.

 

[pgc01]

When you change the value in D2 (the number of digits to retrieve), the formula result does not update unless the function is made volatile.

Rick, thank you. I missed it.


Michael

This means that, in order to have my udf recalculate when you change the number of digits you have to change it.

You either

1 - add also the volatile statement at the beginning of the udf

or

2 - include the number of digits as a parameter, like

Function FiveDigitNo(s As String, lNumDigits As Long) As String
 
With CreateObject("VBScript.RegExp")
    .Pattern = "(?:^|\D)(\d{" & lNumDigits & "})(?!\d)"
    If .Test(s) Then FiveDigitNo = .Execute(s)(0).SubMatches(0)
End With
End Function

This way the udf is not volatile.


Also don't forget to test Rick's code. I'm the biggest fan of Regexes, they are an incredible powerful tool to parse text, but they are heavy. For simple cases like this one the Like operator is much faster and has a simpler syntax.

 

[Michael M]

Thank you PGC and Rick
Rick I lready have one of your snippets that does something similar, ( it removes ALL digits from a string of Alphanumeric Characters), which works a treat.....and also has the like Operator. Thank you for the extra snippet and some helpful commentary.

PGC....I'm hooked. I've downloaded a couple of Tutorials from the web, and have created about 10 UDF's to fit my needs.
Extract filename from text, E-mail address, different date formats, etc.
I'm struggling through, but it's new challenge. Thanks again to you both, although SWMBO isn't happy that I have a new challange !!

 

[Rick Rothstein]

Rick I lready have one of your snippets that does something similar, ( it removes ALL digits from a string of Alphanumeric Characters), which works a treat.....and also has the like Operator.

If you follow my postings for awhile, you will find that I like the Like operator... quite a bit.

PGC....I'm hooked. I've downloaded a couple of Tutorials from the web, and have created about 10 UDF's to fit my needs.
Extract filename from text, E-mail address, different date formats, etc.

I would be interested in seeing those functions, along with a brief description of what they are doing so I don't have to decipher the RegEx expressions (last time I seriously used RegEx was back in the mid-to-late 1980s when my work location was using UNIX on a DIGITAL mini-computer)... I'd like to see if I can simplify them using standard VB constructs. If you are willing to do this, and you don't want to post them here, you can send them directly to me at rickDOTnewsATverizonDOTnet (substituting the upper case letters with the symbols they spell out).

 

[Biz]

If you follow my postings for awhile, you will find that I like the Like operator... quite a bit.


I would be interested in seeing those functions, along with a brief description of what they are doing so I don't have to decipher the RegEx expressions (last time I seriously used RegEx was back in the mid-to-late 1980s when my work location was using UNIX on a DIGITAL mini-computer)... I'd like to see if I can simplify them using standard VB constructs. If you are willing to do this, and you don't want to post them here, you can send them directly to me at rickDOTnewsATverizonDOTnet (substituting the upper case letters with the symbols they spell out).

Like operator reminds me about SQL.

Biz