# Phone Number Formating

#### rookie123

##### New Member
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?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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?

thanks for the welcome

rookie123 said:
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.

Which of the 2 below would be better?
Book2
ABCD
1555.555.5555
2555 555 5555
3(555) 555-5555
4555-555-5555
5(555) 555-5555
6or
7555-555-5555
8555-555-5555
9555-555-5555
10555-555-5555
11555-555-5555
Sheet2

the second ... all 555-555-5555

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.

EXXXXXCEEELENT (*mr. burns-ish)

Appreciate that formula a lot manj!

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

Replies
7
Views
209
Replies
4
Views
207
Replies
5
Views
680
Replies
1
Views
334
Replies
6
Views
794

1,221,007
Messages
6,157,350
Members
451,417
Latest member
Ilu

### 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.

### Which adblocker are you using?

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

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