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

 

[István Hirsch]

Do you want the result in one cell or different cells?

 

[madhav_kulkarni]

Do you want the result in one cell or different cells?

Same cell would be great. Thanks

 

[pgc01]

Hi

Try:

=ExtractCode(A1)

using this udf:

Function ExtractCode(s As String) As String

With CreateObject("VBScript.RegExp")
    .Pattern = ".*? (\d{2,7}-\d\d-\d(?=(,| |$)))|.*"
    .Global = True
    ExtractCode = Trim(.Replace(s, " $1"))
End With
End Function

 

[István Hirsch]

Hi

Try:

=ExtractCode(A1)

using this udf:

Function ExtractCode(s As String) As String

With CreateObject("VBScript.RegExp")
    .Pattern = ".*? (\d{2,7}-\d\d-\d(?=(,| |$)))|.*"
    .Global = True
    ExtractCode = Trim(.Replace(s, " $1"))
End With
End Function

My excel does not extract the substring to be extracted if it is in front position.

 

[pgc01]

Hi István

You are right, but madhav said

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

and so I expected a space before the target, if the target starts at the first character it will not be matched.

But I think you are right, better to also consider the case where a code starts at character 1:

    .Pattern = "(?:^|.*? )(\d{2,7}-\d\d-\d(?=(,| |$)))|.*"

 

[István Hirsch]

Thanks pgc, now it works for substrings in front position, too.

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

Not true for the OP's last substring in post #30.

 

[Rick Rothstein]

For those who might be interested, here is how to write the UDF without using Regular Expressions...

Function ExtractCode(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(Replace(S, ",", " "))
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*[!0-9-]*" Then Parts(X) = ""
  Next
  ExtractCode = Application.Trim(Join(Parts))
End Function

 

[pgc01]

... Not true for the OP's last substring in post #30.

You are right. Should maybe be something like

..extract only digit strings between space and (space or comma) or at the beginning or at the end of the string...

Rick

I tried with

ab --5-- cdefgh 1-2-3-4, 111-222

and got

--5-- 1-2-3-4 111-222

None of these codes respects the specifications.

 

[Rick Rothstein]

Rick

I tried with

ab --5-- cdefgh 1-2-3-4, 111-222

and got

--5-- 1-2-3-4 111-222

None of these codes respects the specifications.

My code failed because I did not read the specifications fully; however, this revised non-RegExp UDF should work correctly (assuming I interpreted the specifications correctly)...

[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) < 7 Or Len(V) > 12 Or V Like "*[!0-9-]*" Or Not V Like String(Application.Max(0, Len(V) - 5), "#") & "-##-#" Then Parts(X) = ""
  Next
  ExtractCode = Application.Trim(Join(Parts))
End Function[/td]
[/tr]
[/table]

 

[madhav_kulkarni]

Glad to see great minds helping me...

probably you guys are close but still the codes have not given what is expected..

Please use my example to try.. (red color below is just for ease of understanding)

32-12-3 1-anfkjdn-2,3-nngnkfngnfd ngfndnfg 454532-34-5 mngfdsngf 7674-46-9, nfkjndsgkfdnm,nkjfdf 768-54-1 nfngfdngfn 96-78-1, 154222-98-3

I would like extract only the text-independent number strings 32-12-3 454532-34-5 7674-46-9 768-54-1 96-78-1 154222-98-3.
Please ignore all the strings where text and digits are mixed (example: 1-anfkjdn-2,3-nngnkfngnfd)..
extract only digit strings with patterns: #######-##-# or ######-##-# or #####-##-# or ####-##-# or ###-##-# or ###-##-#
Please note last "-##-#" is fixed only digit in the 1st set is varying from 2 to 7.
Thank you for helping me out..