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

 

[madhav_kulkarni]

Working . I added extra space at the beginning.

 

[madhav_kulkarni]

Working

 

[Rick Rothstein]

Working . I added extra space at the beginning.

Working

If there are multiple responders and you don't put the name of who you are responding to, we don't know who you meant your comments for. It does not sound like either of these were meant for me so I am wondering if you saw the UDF code I posted in Message #39 yet because it seems to work perfectly without you having to do anything extra.

 

[madhav_kulkarni]

@ Rick, Thanks. Apologies for not adding name of helping person. I was replying to specifically post below it so thought my response would be connected to each of them. Both solutions, by you and pgc01 worked. Thanks a ton to both of you. Glad I reached out to you guys. Amazing stuff!

 

[pgc01]

Hi madhav

Working . I added extra space at the beginning.

My first solution needed a space before because that's what you wrote in your post:

.. extract only digit strings between space and (space or comma)..

István has noticed it and I posted an update in post #35 that does not need the space before at the beginning of the text.

 

[pgc01]

Rick, I believe this will also work:

    If Len(V) > 12 Or Not "00000" & V Like "*#######-##-#" Then Parts(X) = ""

 

[Rick Rothstein]

Rick, I believe this will also work:

    If Len(V) > 12 Or Not "00000" & V Like "*#######-##-#" Then Parts(X) = ""

Of course it will

So those who are interested do not have to go back to find it, here is my code from Message #39 with PGC's excellent suggestion substituted in...

[table="width: 500"]
[tr]
	[td]Function ExtractCode(S As String) As String
  Dim X As Long, V As String, Parts() As String
  Parts = Split(Application.Trim(Replace(S, ",", " ")))
  For X = 0 To UBound(Parts)
    V = Parts(X)
    If Len(V) > 12 Or Not "00000" & V Like "*#######-##-#" Then Parts(X) = ""
  Next
  ExtractCode = Application.Trim(Join(Parts))
End Function[/td]
[/tr]
[/table]

 

[madhav_kulkarni]

Thanks Rick and Pgc01. I appreciate your VB expertise.

I wish to share the code with team members but they will find it easy to click and execute the code rather then pasting the code in desired excel after Alt +F11..
How to convert the UDF into a macro.. I know how to create a ribbon button for a macro..
Assuming that the source data is in column A for variable numbers of rows, how to execute the code and extract the numbers in Column B.
Please advise and/or help.

 

[wrxdude7]

Could someone translate (?:^|\D)

The OR operator with only an anchor before it is throwing me off

 

[pgc01]

Hi

.Pattern = "(?:^|\D)(\d{5})(?!\d)"

I'm looking for a sequence of exactly 5 digits.

The exactly means that I'm looking for a sequence of 5 digits that is neither preceded not followed by a digit.

The (?:^|\D) means not preceded by a digit, which is
- at the beginning of the string
OR
- preceded by a character that is not a digit

Remark:
As you may know vba has an old regex engine that does not recognize lookbehinds.
With a more modern version of regex you could use the simpler

.Pattern = "(?<!--\d)(\d{5})(?!\d)"

which would be more simetrical and easier to understand.