Email:      Pass:    Pass?
Subscribe for Free Excel tips & more!
E-mail:
Advertisements


Free Excel Forum

Change Xxxxxxxxxx To Xxx-xxx-xxxx?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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



Similar Excel Video Tutorials

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 have a long column of phone numbers. I need to put all the numbers into (xxx) xxx-xxxx format.

Currently they are in three different formats.
1) xxx-xxx-xxxx
2) xxxxxxxxxx
3) (xxx) xxx-xxxx

Thank you.

Gene

PHONE formatting. Is there a way to create a macro to format the VALUE of the phone to be either (XXX) XXX-XXX or XXX-XXX-XXXX ??? I obviously can get it to look like this, but the value is just XXXXXXXXXX.


Thanks much in advance!!!




Hi!
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?
Also
How can I convert format (xxx)xxx-xxxx to just xxxxxxxxxx?
Thanks

Trying to go from a standard phone number format (xxx-xxx-xxxx) to a 10 digit number (xxxxxxxxxx). None of the format features appear to be working. Does anyone have a clue how to take out the "-" from the number?




Hi

I hope you are all well.

I was wondering if there is a way to highlight cells that do not match your criteria similar to an input mask in Access.

For example:

I am using a right (A4, 7) formula on a phone number to find unique records.

However, the issue occurs when the format I receive has mutliple phone number configurations:
i.e: 1. (XX) XXXX XXXX (perfect)
2. (XX) XX XXXXXX
3. (XX) XXXXXXXX
4. XXXXXXXXXX

Therefore, if there is a way to highlight results that do not come back as X XXXX.

Thanks for any help that is given.

Matt

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




Hello! So I need help with a macro. I'm totally new to macros so I don't really understand how much of it works. I have data from US phone numbers currently, but later on I'll have phone numbers from Switzerland/GB/Australia/etc. Currently though, I'd like to focus on the macro for US phone numbers.

What I need the macro to do is take phone numbers from such formats: 123.123.1234, 123-123-1234, 123 123 1234, 1231231234, etc. and have them changed to (123) 123-1234.

It would also be preferable if the macro could tackle a whole column. In this case I need the macro to change data in column "S". And it would be nice if I could just change the column ID within the macro later on so the macro can focus on other columns after it has finished one. If that makes sense.

* If anyone does have a macro to change out-of-country phone numbers (e.g. (0x) xxxx xxxx or xxxxx-xxxxxx) into the (123) 123-1234 format, that would be great! My data does have MailingCountry ID's as well. Which are organized as United States of America, Switzerland, Australia, Uruguay, etc.

Thank you for any help!

I have two lists of about 5000 names and addresses each. The phone number column has numbers listed like this +1.xxx.xxx.xxxx for the first spreadsheet and the other spreadsheet is like this xxx.xxx.xxxx
I would like to change them both to xxx-xxx-xxxx
Anybody have an idea how I can make this change easily. I don't want to have to change each entry manually.


Hello all,

I have a user form that have a phone and a fax field that i dont want letters entered.

i have in my code

If Not IsNumeric(Phone.Value) Then
MsgBox "Phone number not valid"
Exit Sub
End If

But dosnt allow number with spaces
ideally the number format should only allow XX XXXX XXXX format

can someone help me write my code ?


I have several spreadsheets that have thousand of phone numbers
i need the phone number values to be like (123) 233-5121
If I have a number like 1232335121 I can change the format to look like a phone number but that
does not change the actual value.
Is there a way to either
1. copy the format and past it as a value.
I tried this with copy and past values but of course that did no good.
2. if 1. is not possible is there some sort of macro or something that I can create that will go in and put parentheses around the 1st 3 numbers add a space and then a - in between 6th and 7th number.

Thanks


I have several spreadsheets that have thousand of phone numbers
i need the phone number values to be like (123) 233-5121
If I have a number like 1232335121 I can change the format to look like a phone number but that
does not change the actual value.
Is there a way to either
1. copy the format and past it as a value.
I tried this with copy and past values but of course that did no good.
2. if 1. is not possible is there some sort of macro or something that I can create that will go in and put parentheses around the 1st 3 numbers add a space and then a - in between 6th and 7th number.

Thanks


I have a column of phone numbers formatted as (xxx) xxx-xxxx, but i would like to convert them to xxxxxxxxxx; parathesis and hyphen removed. Any simple solutions?


I have a spreadsheet of 138 phone #'s (including area codes).

About 10 of the #'s have a letter in them (i.e. 734-R42-7422) (and no, I can't just change the R to "7" - that would be too easy)

Obviously, since they have both text and numerical characters, they won't format properly as phone numbers.

Does anyone know how can I format them to show as phone #'s ?


Hey, im looking to format a number of cells into a phone number format. I have a rather large excel spreadsheet of around 900 phone numbers, they are currently in different formats such as:

0870 8888888
+44 0800 7777777
+800 (900) 666666
0980 888888

These are obviously just example numbers and not real ones, as you can see they are all over the place and in the wrong format that i need them in. Changing them all manually would take me forever so ive tried to create a custom formatting rule to have them represented like this:

+44 (800) 888888

So basically they must all begin with +44 then the next 3 digits must be in brackets however the zero must be trimmed from the start, and the following digits must be on their own, for example:

0800 999999 would turn into +44 (800) 9999999

Now ive managed to change single numbers into this format, for example
055453534 into +44 (554) 53534 using the custom format: +44 (#) however i have had no luck with numbers that are seperated. If anyone could help me with this i would be very grateful.

Thanx in advance.


Hi guys, is there a quick way to do this? I need to import a .csv file so I can send out txts from my phone providers e-txt site. They want the phone numbers to be the international format, so they need to be 642xxxxxxxx and currently the numbers have been entered sporadically so they range from 021 xxxxxx to 274xx xxxx etc.

Is there any way at all I can avoid manually changing each of my 200 cells?

Thanks in advance for advice,
Jo


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

Hello,
I am trying to setup a contact list sheet. I can format all the other cells the way I like, but when I try to format the phone number column with format cells\special\phone number numbers are being displayed as (for the phone number (123) 123-1234 entered as 1231231234) : (1) 231-2312. For another real world example this phone number (602) 256-6700 entered as 6022566700 is being displayed as (6) 022-5667. Other phone numbers are having there last 4 digits rounded. I.E. 4554 is becoming 46.
I have no idea what has gone wrong! I have tried opening a new workbook, re-starting excel etc...
Thanks 1 millions times for any help.
-Todd

okay, I decided to skip that and went on to some other stuff. Now 2400 is being displayed as 24 no matter what I do or where I put it, even in a new workbook.


Hello!

I have a column of phone numbers, some are formatted like this 123-456-7892 and others are like this (456) 789-1234. I need them all to format as (456) 789-1234. I have tried the phone number format in excel and it recognizes both of these formats as phone numbers, so it does not convert one to the other. I'm sure there has to be a formula that can perform this, I just don't know it.

If any one can help me, I would greatly appreciate it!

Thank You!


Hello I currently have phone numbers formated like this:
(XXX)XXX-XXXX
but I would like them to be formated like this:
XXXXXXXXXX
basically remove the ()-
I could remove them in VB or find/replace but it would be best if there was a simple formula to use.
Thanks!


For some reason my Excel program has changed the format on Phone numbers. For example when I set a cell to use the phone number format it should take the numbers 1234567890 and show them in the cell as (123) 456-7890 but they don't. They now show in the cell as (1) 234-5678 can anybody tell me how to fix this.
Also, I have noticed another change ie., I in a cell I enter 9. it will come out as 0.09
Any suggestions?


Hi,

I am trying to convert an excel sheet to csv format, however, i can see some of the phone numbers are getting converted to negative numbers (-xxxx). Is there any way i could restrict this?

Any help would be appreciated.

TIA

Regards, John


Hi everyone,

Sorry if this is an easy one. I have a very long list of phone numbers. They are currently in the following format:

02 1234 5678

I want to change this so they look like:

02 12345678

I want the cell to include the 0 at the front (not just look like it) so when you click on the cell it should show the 0 in the front in formula bar.

Some phone numbers in the database are different. Example:

13 1234

In all cases I want 2 numbers, a space and the rest of the numbers.

Any help would be much appreciated.

Kind regards,

Dane


Sorry, I dont know how to insert an html image. I have a column where each cell contains 4 hard returns. The first line is a home phone (return), the second line is fax nbr (return), etc. I need a macro that looks for "cell", then parses the cell phone number to the left of it, and adds the number to the cell on the right. Im using Excel 2003 thanks

CONTACT INFO XXX-XXX-XXXX Hm
XXX-XXX-XXXX FAX
XXX-XXX-XXXX OFC
XXX-XXX-XXXX CELL XXX-XXX-XXXX Hm
XXX-XXX-XXXX FAX
XXX-XXX-XXXX OFC
XXX-XXX-XXXX CELL XXX-XXX-XXXX Hm
XXX-XXX-XXXX FAX
XXX-XXX-XXXX OFC
XXX-XXX-XXXX CELL


I have phone numbers entered in the (222) 333-4444 format and need to change them to the 222-333-4444 format. Is there a Find/Replace or other formula that can accomplish this? If so, please provide an example. Thx!


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?