changing formating of phone numbers

bbbb1234

Board Regular
Joined
Aug 8, 2002
Messages
150
Office Version
  1. 365
Platform
  1. Windows
I have the following data in column B. I want a universal formula in Col c to change what is in Col B as noted for each row.
Book2
BCDE
4S:Cel:785-685-7505 Annie:Cel:785-685-4804S:(785) 685-7505 Annie:(665)685-4804
5G:Cel:785-685-5954G:(785)685-5954
6Jo:Cel:785-686-4600Jo:(785)686-4600
7Jo:753-937-3839 K:Cel:785-787-7676 S:Cel:837-787-7749Jo:(753)937-3839 K:(785)787-7676 S:(837)787-7749
Sheet1


Any ideas?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Well, that almost gets it but only covers the cases where there is one phone number in the cell. Thoughts on how to cover all cases?
 
Upvote 0
bbbb1234 said:
Well, that almost gets it but only covers the cases where there is one phone number in the cell. Thoughts on how to cover all cases?

I failed to notice you had multiple phone numbers in a cell. Otherwise, I would not have replied. Such data can better be dealt with VBA.
 
Upvote 0
UDF
1) hit Alt + F11 to open VB editor
2) go to Insert -> Module then paste the code
3) hit x to close window and get back to Excel

in cell

=bbbb(b4)

format the cell as rap text

Code:
Function bbbb(txt As String) As String
Dim x, y, i As Long, res As String
txt = Replace(txt, "Cel:", "")
x = Split(txt, Chr(10))
ReDim y(UBound(x))
For i = 0 To UBound(x)
    z = InStr(x(i), "-")
    x(i) = Application.Replace(x(i), z, 1, Chr(32))
    x(i) = Replace(x(i), String(2, Chr(32)), Chr(32))
    y = Split(x(i))
    res = res & y(0) & "(" & y(1) & ") " & y(2) & vbLf
Next
bbbb = Left(res, Len(res) - 1)
Erase x, y
End Function
 
Upvote 0
Another alternative:
Code:
Function ConvertPh(Trg As Range) As String
    Application.Volatile
    
    myarray = Split(Trg, ":")
    
    For x = LBound(myarray) To UBound(myarray)
        If Asc(Left(Trim(myarray(x)), 1)) > 47 _
                And Asc(Left(Trim(myarray(x)), 1)) < 58 Then
            
            myarray(x) = " (" & Trim(myarray(x))
            
            myarray(x) = Application.WorksheetFunction _
                            .Substitute(myarray(x), "-", ") ", 1)
        
        End If
    Next
    
    ConvertPh = Join(myarray, ":")
End Function
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top