Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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


View Answers     

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 ...
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 ...
Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...

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!

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 ?


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.


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!


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?



Hi,

I am downloading an online phone bill in a csv format. When opening the file, the phone numbers drop the leading 0. Example phone number is 0400123456.
This number is displaying as 400123456. I need this in a number format so I do not want to convert to text.
I know that if I can convert the format to 0400 123 456 (grouping the numbers with spaces between) then excel leaves the zero in place, but how do I do that without going into each cell individually and re typing it?
If I use custom format with a 0### ### ### then the formula vlookup will still read the cell as not having the 0 and i need to use vlookup.
My goal is to have a number format with the leading zero and grouped with #### ### ###
Help please as I am stuck


I need to present one or two phone numbers in one cell. If just one, then it should be just

(###) ###-####

However, if there are two is should be

(###) ###-####
(###) ###-####

inside the same cell.

I have been able to get as far as stacking two numbers in the correct format using ALT+0010, but then I run into the 15 digit limitation of excel. I can't seem to figure out how to format the numbers as text to get around the limitation AND allow it to use the single phone number or double phone number format.

I can get

(123) 456-7890

and

(123) 456-7890
(123) 450-0000

But not

(123) 456-7890
(123) 456-7890

Thanks!

I'm working on a database file that has both foreign and domestic phone numbers in it. I need to add a "+" sign in front of all the numbers, what is the easiest way to go about this? Something is off in the formatting because when ever I try to do a custom format it doesn't change anything, nor does it change when I convert the format to a number. I am pretty sure all spaces have been removed but I cannot be certain. I've attached a copy for you to look at.

Thank you,

Dustin


Is there an easy way to apply a constant format to phone numbers in a column all input in different formats that are all typed in differently into one format like (515) 123-4567.

For example:
A
(515) 123-4567
515-345-6789
1-515-987-6554
5151234567
(515)345-6789

I would like all cells under column A to be formatted (xxx) xxx-xxxx

Thanks


I have an excel sheet with a row of numbers example below. These numbers represent the length of time that a telephone call was. The problem is they are decimal numbers (units of 100) but they represent seconds and minutes of a phone call which change up one number when the seconds hit 60.


1 This would equal a 1 second phone call
230 This would equal a 2 minute and 30 second phone call
239 This would equal a 2 minute and 39 second phone call
200 This would equal a 2 minute and 00 second phone call
133
245
300
230
130

The database I import this sheet into is not used to accepting these times, the previous spreadsheets the company has sent me have the timer in a format that is

0:01
2:30
2:39
2:00

etc

Is there a formula I can use to change this decimal format number into the minutes:seconds that I require?

I can provide more information if required sadly I am not very skilled up on this type of thing.

Many thanks.


I have a list of phone numbers that show up as ########## and I want it to look like (###) ###-####. I know I can use the tools, format Cells, number, special, and choose format. What happens is that even when I do this and then do a merge the number comes out like ########## rather than (###) ###-####. When I put my cursor in the (###) ###-#### field it shows the original formatted number as one long number. How can I make excel keep the formatting of the phone number and then use it in a merge?

Thank you,
DEllis


I have a list of phone numbers downloaded into Excel from an external database. There was not consistency on how the phone numbers were entered in the database. Some have space between the numbers (xxx xxx xxxx), some have dashes (xxx-xxx-xxxx), some have ( ) marks ((xxx) xxx xxxx), or some combination of all the above.

I need a macro that will go down the column and remove all extra characters other than the numbers to put them all into a format of ##########

I have basic macro knowledge, but I am at a loss on this one.

Any help would be greatly appreciated.