Extracting Multiple Numbers from a Text String

[theboylow]

Hi,

I have a question on extracting multiple numbers from a text string - not sure if this can be done but am looking for a forumla or a way to help me do this.

In cell a1 I have the following text:

The business can be contacted on 01112223333 or 01113334444 or if not available 01114445555


I'd like to see the 3 numbers shown in b1 or alternatively the three numbers extracted to show in three different cells e.g. b1, c1, d1 with one number per cell


Can anyone help?

 

[XOR LX]

Hi.

In your one example all strings begin with a zero and all are 11 characters in length. I presume then that this is always the case?

If not, can you please provide more realistic and varied examples?

Regards

 

[theboylow]

Yes 99% of the numbers I need to extract will begin with a zero and will be eleven digits in length. 1% of the numbers will be in a format like this 0111 222 3333 but I'll just extract these numbers manually unless there's a way to do this via another formula for numbers in that format.

 

[XOR LX]

And will there ever be any occurrences of a zero within a string which is not the leading digit of a value to be extracted?

Regards

 

[mikerickson]

Are all the numbers in question integers?

 

[theboylow]

yes - the number could be 01110223333 for example

 

[theboylow]

yes mike all numbers will be positive integers - the numbers are actually UK phone numbers if that helps

 

[XOR LX]

Array formula**:

=MID($A1,SMALL(IF(MMULT(0+(ISNUMBER(0+MID(MID($A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN($A1)-10)),11),{1,2,3,4,5,6,7,8,9,10,11},1))),{1;1;1;1;1;1;1;1;1;1;1})=11,ROW(INDEX(A:A,1):INDEX(A:A,LEN($A1)-10))),COLUMNS($A:A)),11)

Copy to the right as required. Add an IFERROR statement to mask errors if desired.

Note that I didn't write anything in to this set-up to account for strings which also contain numbers with more than 11 digits. Any such numbers will cause this set-up to give incorrect results (although I can modify the formula with a few tweaks if such cases are possible - let me know).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[theboylow]

Array formula**:

=MID($A1,SMALL(IF(MMULT(0+(ISNUMBER(0+MID(MID($A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN($A1)-10)),11),{1,2,3,4,5,6,7,8,9,10,11},1))),{1;1;1;1;1;1;1;1;1;1;1})=11,ROW(INDEX(A:A,1):INDEX(A:A,LEN($A1)-10))),COLUMNS($A:A)),11)

Copy to the right as required. Add an IFERROR statement to mask errors if desired.

Note that I didn't write anything in to this set-up to account for strings which also contain numbers with more than 11 digits. Any such numbers will cause this set-up to give incorrect results (although I can modify the formula with a few tweaks if such cases are possible - let me know).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Not sure if I'm doing something wrong but if I copy this formula into cell b1 and apply it to my text in cell a1 the only thing is returned is "the busines" which is the first 11 characters of my example text - shown again below for reference

The business can be contacted on 01112223333 or 01113334444 or if not available 01114445555

 

[Rick Rothstein]

Here is a UDF (user defined function), the first will pull out all of the 11-digit numbers and put them in a single cell as a comma-delimited list. You would call it like this....

=UKPhoneNumbers(A1)

Here is the code for it...

Function UKPhoneNumbers(ByVal S As String) As String
  Dim X As Long, PhoneNumbers() As String
  S = Replace(S, " ", "")
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  PhoneNumbers = Split(Application.Trim(S))
  For X = 0 To UBound(PhoneNumbers)
    If Len(PhoneNumbers(X)) <> 11 Then PhoneNumbers(X) = ""
  Next
  UKPhoneNumber = Replace(Application.Trim(Join(PhoneNumbers)), " ", ", ")
End Function

NOTE: Any numbers listed with spaces between parts of them (like 0111 222 3333) will have their spaces removed in the output from the UDF.


This next UDF (note the difference in spelling... the above code ends in an "s", this next one does not) will place a single 11-digit number in the cell which you would specify by its second argument. You would call the UDF by placing it in the cell you want the first number to go in and then copying it across for as many cells as you think you will ever have phone numbers for at a maximum (don't worry if there are not enough phone numbers as the function will output the empty string "" if it runs out of 11-digit numbers). These formulas can then be copied down as needed...

=UKPhoneNumber($A1,COLUMNS($A:A))

Here is the code for it...

Function UKPhoneNumber(ByVal S As String, Number As Long) As String
  Dim X As Long, PhoneNumbers() As String
  S = Replace(S, " ", "")
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  PhoneNumbers = Split(Application.Trim(S))
  For X = 0 To UBound(PhoneNumbers)
    If Len(PhoneNumbers(X)) <> 11 Then PhoneNumbers(X) = ""
  Next
  PhoneNumbers = Split(Application.Trim(Join(PhoneNumbers)))
  If Number <= UBound(PhoneNumbers) + 1 Then UKPhoneNumber = PhoneNumbers(Number - 1)
End Function

NOTE: Any numbers listed with spaces between parts of them (like 0111 222 3333) will have their spaces removed in the output from the UDF.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. See examples provided with each descriptions for each UDF shown above.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.