changing formating of phone numbers

bbbb1234

Board Regular
Joined
Aug 8, 2002
Messages
109
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?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
=MID(B1,1,SEARCH(":",B1))&" ("&MID(B1,SEARCH("-",B1)-3,3)&") "&REPLACE(B1,1,SEARCH("-",B1),"")
 

bbbb1234

Board Regular
Joined
Aug 8, 2002
Messages
109
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
 

BJungheim

Well-known Member
Joined
Jul 24, 2004
Messages
914
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,229
Messages
5,571,019
Members
412,354
Latest member
Stj99
Top