Extracting Number String from a Text String Cell

[Chubba]

Hi

I have a list of cells with text that contains a 9 digit number that I'd like to extract. The issue I have is that this 9 digit reference can appear anywhere in the cell and further complicated in that the cell could contain other numerical characters which I won't need so, for example, it may look like (highlighted in bold the number I wish to extract):

Test 259 7456 20 alpha
Test on 22/09/22 964 5489 30
258 6321 95 completed 3 December
15 June begin 967 5841 32 over

I was thinking perhaps there could be a way to extract the number that fits the format "xxx xxxx xx". Is that possible for Excel 2010?

Thanks

 

[Sanjeev1976]

If your first number is in cell A1, you can try the following formula and then pull down the formula :

=IFERROR(MID(A1,SEARCH("??? ???? ??",A1),11),"NA")

 

[RoryA]

That won't restrict to numbers though. If you had:

new Test 259 7456 20 alpha

it would return "new Test 25" and not the number.

 

[Chubba]

Thank you Sanjeev1976. That worked for 95% of the cases and was just about to post exactly what RoryA has said. While that didn't appear in my examples, that is a possibility so is there a way to search "??? ???? ??" but restrict to just numbers?

Thanks again

 

[RoryA]

Which version of Excel do you have?

 

[Chubba]

Which version of Excel do you have?

I have Excel 2010

 

[RoryA]

I suggest you update your profile to include that, as it will restrict a lot of the options you would have available with more current versions.

 

[Chubba]

I suggest you update your profile to include that, as it will restrict a lot of the options you would have available with more current versions.

Thanks for the advice. Now updated

 

[RoryA]

You could try a UDF like this:

Function GetNum(checkVal As String) As String
   If checkVal Like "*### #### ##*" Then
      With CreateObject("vbscript.regexp")
         .Pattern = "\d{3} \d{4} [\d]{2}"
         .Global = True
         GetNum = .Execute(checkVal)(0)
      End With
   End If
End Function

Used as =GetNum(A1)

 

[Glenn Kennedy]

Formula:

=TEXT(MAX(IFERROR(VALUE(MID(SUBSTITUTE(A1," ",""),ROW($A$1:$A$300),9)),0)),"### #### ##")