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...
Code:
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...
Code:
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.
