Change Xxxxxxxxxx To Xxx-xxx-xxxx?

I have large quantity phone numbers in this format 2392617177, and I like to
change all the phone number to this 239-261-7177 format. How can I do it
quickly? Please help….

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

Change the Default Number of Worksheets Created in a New Excel Workbook
Learn how to change the number of worksheets that are in new Excel workbooks. Excel workbooks always have 3 worksh ...
Email List of People from Excel Using a Macro
Send emails to everyone in a list in Excel using a macro. The message for the email can either come from another co ...
Change the Order of Tabs in Excel
How to change the order of the tabs in your spreadsheet. This is rather easy to do and makes your spreadsheets much ...
Change Axis Units on Charts in Excel
You can change the size of the units on a chart axis, their interval, where they start, where they finish, and mor ...

Helpful Excel Macros

Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells as a Scientific Number in Excel Number Formatting
- This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put

Similar Topics

I'm pulling large amounts of data to excel file from 2 databeses (websites)
I have one database of phone numbers that I download to excel as xxx-xxx-xxxx
Another database of phone numbers I download to excel as (xxx)xxx-xxxx
Often I need to find a match of a multiple phone numbers, so I need to have them in the same format.
How can I convert in excel one to another between those 2 formats using function or formula?
How can I convert format (xxx)xxx-xxxx to just xxxxxxxxxx?

I have a column of phone numbers, xxxxxxxxxx, I would like to put a space to separate for easy viewing xxx xxx xxxx. ( some of the numbers have a 1 in front, so I would like it to display x xxx xxx xxxx.

Thank you in advance,

My question is that I have address and phone numbers posted to a workbook. What I would like to do is have excel delete everything except for the phone numbers. The format for phone numbers is constant, (xxx)xxx-xxxx, everything else is name and address.

Is there a way to do this?

Thank you again

Eric Anderson

I can't get a column of numbers, 9999999999, to format as a phone number,
(999) 999-9999. I've tried phone number format, as well as what I found in
this discussion group and nothing has worked. Any new suggestions?

Our office recently changed the format we use for phone numbers - from dashes to periods. I want to make a custom format for cells so that even if I slip and use dashes in the phone number, Excel will know to display the phone number with periods. I can't find the way to type the format so that Excel will accept it and to make the changes. Any help would be appreciated!


I am trying to format a TextBox on a UserForm to display a phone
number in xxx-xxx-xxxx format when I exit the TextBox.

Any help would be appreciated.



I have one column that contains several possible phone numbers that looks like

Office phone: 416.555.0442
Office phone: 306.555.5322, Office fax: 306.555.5347
Office phone: 306.555.7506, Office fax: 306.555.7500, Cell phone: 306.555.3584, Home phone: 306.555.6990

I need to separate this into 4 individual columns just leaving the phone numbers in the column


I would like to convert a column of telephone numbers that are currently in
the format of: ########## to the format of: ###-###-####.

I have added a column next to the existing phone number column with the new
format listed above, but when I move the old number to the new number, the
format does not change. It will only change to the new format if I manually
enter in each number.

Does anyone know how I could convert the existing numbers to the above
telephone format?

Thanks in advance for your help!

I have phone numbers in this format (111) 234-5678. I need to remove all the dashes, spaces and parenthesis so that it is formatted like this 1112345678. Any suggestions will be appreciated,



I am trying to write a macro that converts a large list of Customer details from a vertical to a horizontal format.

Each Customer has a unique sequential number running from 1 but the details are not always the same (e.g. in the below Phone does not always appear).

Also, not all row details will be taken throught to the horizontal list (e.g. in the below Specifics 1 and Specifics 2 are not required)

Current format is per the below:
Name 1 Address ABC Phone 1234 Specifics 1 TTT Name 2 Amount 25 Address XYZ Name 3 Amount 500 Address PQRS Phone 567 Name 4 Address ABC Phone 1234 Name 5 Amount 25 Address XYZ Name 6 Amount 500 Address PQRS Phone 567 Specifics 2 ZZZ

Format after should be:

Name Amount Address Phone 1 ABC 1234 2 25 XYZ 3 500 PQRS 567 4 ABC 1234 5 25 XYZ 6 500 PQRS 567

Would anyone be able to help?


I just got an address list with phone numbers in different formats: (000)
000-0000 and 000-000-0000. I want to convert them all to one format. The
format cells way does not seem to work and the search in this archive hasn't
yielded any solutions. Can you take an existing list like this and convert
it to a standard format? Thanks ahead for your help.


I have a spreadsheet with phone numbers in the format of (111) 222-3333. I need to convert them all to 1112223333. Any Ideas?

I've tried a custom format for entering credit card numbers (four groups of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx.

I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but each of these causes the last digit to change to zero. So if I enter
5415779800902512 I get 5415-7798-0090-2510.

Anybody already solved this problem?

Locked - Title does not comply with forum rules

I have a cell which is used to store a phone number the format normally is:

xxx-xxx-xxxx where x= any number. When I brought this database base over from Access approximately 1100 cells came over with a format of this:

xxx-xxxab-xxxx. Where ab equals 78 in 770 different cells and where ab = 70 in 330 cells. I have tried using find/replace along with using wilcards. I can select the records perfectly, but how do I go about getting rid of those two extra characters in those cells? They are always in the same position in each of the 1100 cells. Thanks in advance for any help.

So I have an old spreadsheet with a lot of 7 digit phone numbers in a column (like 555-5555). I need to figure out how to ID all numbers using the ***-**** format and prefacing them with the area code 510 so it adds the area code in front of the number. There are some entries that have different area codes (415, 925, et al) or already have the 510 area code which will need to be ignored.

How do I accomplish this?

I have 2 cells, one has a phone number and the other has a name. Example A1 Has Phone Number B1 Name. The phone number cell has a one in front of the number (12155551212) I need to remove the 1 from it.

Any suggestions ?

I created a custom format for credit card numbers using XXXX-XXXX-XXXX-XXXX.
But when I key a number into the formatted cell, the last (rightmost) digit converts to zero.

So when I enter, for example, 5511246190465589 the cell displays 5511-2461-9046-5580.

Any ideas?

Hi All

First of all, if this has already been posted and resolved, SORRY!. I could not find it when searching.

My question is quite simple, yet, I cant find an answer/solution as yet.

I wish to enter a phone number in to excel (using 2007 version) in the following way, (01234) 456789 (5 digits for area code and 6 digits for number) - I have figured out that the format to use is something like '(0####)_######'

However some locations where I live still have a 5 digit phone number and therefore I will need to have the facility to have a number like (01234) 56789 (5 digits for area code and 5 digits for number)

My problem is that what ever combination of codes I try, I end up with a mess Its the occasional 10 digit combination is the route of the problem and would love to see if I can overcome this.

Any suggestions or help would be greatly appreciated.


I have a list of mobile phone numbers from various countries. However, I do not know which country each entry is from. Ideally I would like to have a macro that looks at each number, compares to a global list of PSTN structure to determine which part of the phone number is the country code (generally the first 1-3 digits), and then put the country in a separate column.

I am certain all numbers are formatted correctly, so it is only a matter of finding out which part is the country code and putting a value for the country.

Anyone come across this one before?


I have a column called "duration", which shows the length of a phone call made. It has been given to me in the format of hh:mm:ss, so that 00:00:35 means a 35 second call, 00:15:42 is a 15 minutes and 42 second call etc.

In essence, though, because of the format being a time format, these numbers really mean 12:00:35 am and 12:15:42 am, that is, a time of day.

I need to convert 00:00:35 into "35 seconds", and then round up to the nearest minute. So in this example, 00:00:35 should equal 1 and 00:15:42 should equal 16.

The roundup function is not working due to the numbers being in time format and I am out of ideas on how to convert seconds into whole numbers.

Is there any easy way to do this? Any ideas? Thanks in advance.

I am a supervisor in a call center and I have an excel spread sheet for phone agents when an agent comes to work in the morning they log into there phone and I have a program that records the time they are in the phone and how many calls they take. When they go on break or lunch or step away from there desk they go into what is called AUX time. At the end of the day I run a report and it shows me the total length they were logged in lets say 9 hours 2 minutes 57 seconds and on the report it shows it as 09:02:57 . Then it breaks it down into ACD (which is the time they were actually on the phone) and AUX time so lets say they were in AUX time 2 hours 14 minutes and 28 seconds that is displayed in the same format of 02:14:28. So at the end of the day I input that into an excel spread sheet I have created that automatically tells me how long they were on the phone taking calls so in this case they would have been on the phone form 7 hours 28 minutes 29 seconds7:28:29. I then have to manully figure up that time into a decimal number so I have to figure up that they were on the phone for 7.5 hours I then have to manually figure up the calls per hour average so on the report it shows me how many calls they did for the day so if they took 56 calls for the day I take 56/7.5=7.5 calls per hour because we round to the 10th. What I want to know is if there is anything I can do to have the time automatically converted into decimal and then when I input the calls if it will automatically figure the calls per hour.

I am trying to match a phone number to a list of country codes in excel to know the country.. the problem is that I want to match the first x characters of the phone number to the country code (where x is the length of the country code in the list) as not all the country codes have the same length (eg. US:1 UK: 44 Jordan:962, UAE:971, Bahrain:973).

Phone Numbers Column looks as below:

Country Code sheet contains in two cols
1 USA 44 UK 91 India 962 Jordan 971 UAE 973 Bahrain

I need to mark each number with the country name.
Hope I am clear explaining the requirement.

Greetings everyone,

I'm a medium level Excel user. I've been having problem with converting and sorting the data according to the format I wanted. Attached in the link are the sample data and the format that I intended to create.

For your info, I've had extract the data from actual online statement and compiled into single sheets. Its a lot of data, I've run out of imagination & formulas. Perhaps someone who is a master in VBA would help me.

Thank you in advance.

I want to limit the characters that the user can input in the phone number
field. Should I use conditional formatting for that?
Any help is appreciated,
Thank you, Candice

Hi guys,
I have a column of dates initially inputted in text format (25/Jan/2010) and I need to change the format to date (25-Jan-2010).
I change the whole columns format using "Format cells..", however the format does not change until I click on every cell and click Enter afterwards. As there are over 200 entries is there a faster way to change them all at once?

Thanks a lot!