Phone Number Formating

[rookie123]

Alright I'm having some issues where I have a column of phone numbers that have came in all sorts of foramts such as

555.555.5555
555 555 5555
(555) 555-5555
555-555-5555
5555555555

The problem is that last one ... I can use find and replace to get all the phone numbers to be 555-555-5555 except for the 5555555555 ones. I can't seem to get them to format correctly. Any tips for getting the to format right?

 

[just_jon]

Welcome to MrExcel - what format do you wish to end up with?

And do you want it to be a part of the value of the cell, or merely a custom format over plain data?

 

[rookie123]

thanks for the welcome

I can use find and replace to get all the phone numbers to be 555-555-5555 except for the 5555555555 ones.

So 555-555-5555 is the format I want the numbers in this cell to be. Everythign is in 555-555-5555 format except for ones that are in 5555555555 format.

I am going to export it as a csv file once they are formated so I'd like the cell value to be 555-555-5555 I'd think.

 

[just_jon]

Which of the 2 below would be better?

Book2
	A	B	C	D
1	555.555.5555
2	555 555 5555
3	(555) 555-5555
4	555-555-5555
5	(555) 555-5555
6	or
7	555-555-5555
8	555-555-5555
9	555-555-5555
10	555-555-5555
11	555-555-5555
Sheet2

 

[rookie123]

the second ... all 555-555-5555

 

[klb]

Insert a column to the left of your telephone numbers and use the following formula. This assumes your telephone numbers are in column b and are formatted as text.

=IF(LEFT(b1,4)="-",B1,LEFT(B14,3)&"-"&MID(B1,5,3)&"-"&RIGHT(B1,4))

Copy the formula down till the end of your data. Then copy - paste special - values over your original column of telephone numbers and delete the column that you inserted.

 

[rookie123]

EXXXXXCEEELENT (*mr. burns-ish)


Appreciate that formula a lot manj!

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> PhoneFmt()
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">With</SPAN> Selection
    .Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>
    .Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>
    .Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>
    .Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>
    .Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=<SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection
    <SPAN style="color:#00007F">If</SPAN> Len(c.Value) = 10 <SPAN style="color:#00007F">Then</SPAN>
        c.Value = Left(c.Value, 3) & "-" & Mid(c.Value, 4, 3) & "-" & Right(c.Value, 4)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Will convert the current selected range.