excel number format

[hounddog1]

OK, I give...I have a spreadsheet with a phone number field that I need to export to another database. The prob is that these #'s were entered in different formats, ie, (###) ###-#### or ###-###-#### or ### ### ####, etc. I need to format these to an international format like ##########. How can I do this?

 

[xlhelp]

Can you not just highlight the range, go to Edit, Clear, Formats? You may want to apply a new format after that.

 

[hounddog1]

I tried that but to no avail. The ideal is to drop the () for the area code and eliminate the -'s and spaces and only display the ten digit number.

 

[Hasit]

Use formulas to replace the contents of the cell and place just the numbers (without the "()"s and the "-"s) in a new cell. Then export the data plus the new cell content.

To achieve this, use the REPLACE function as a starter.....

 

[hounddog1]

Don't want to sound stupid but, could you give me a specific example in layman's terms?

 

[carrr]

Something like this is what (I think) Hasit is saying...

if your phone # is 878-987-9087

=REPLACE(A1,4,1,"&quot

knocks out your first hyphen

=REPLACE(B1,7,1,"&quot

knocks out your second hyphen, etc.

 

[hounddog1]

That sounds logical. If the format of the numbers in the existing sheet is not consistent (one cell is (803) 798-9526 and another is 803-456-5236 and yet another is (803)523-9621) is there a way to easily move, copy paste or whatever, just the numbers so that what's derrived is a single, ten digit number?

 

[carrr]

I'm sitting here smacking myself in the forehead with cold fry from lunch....

Highlight the column with the numbers in it.
Go to Edit, Replace, and start replacing your, (, ), - characters with blanks.

You could record this into a macro if you're gonna have to do this on any sort of rotation.

 

[dianemarie]

Hi, I would highlight the column and do a Replace. (Edit from the main menu and then Replace.) You'll have to do each character separately but it's quick. First put ( in the Find box and nothing in the Replace and choose Replace All. Then do the ). Then do -. When you're down to plain numbers, you should be able to then format the entire column as phone numbers. If it won't let you, copy and paste values to a new column, then try again.

 

[hounddog1]

Thanks to all!!! Got It!!! Duhhhh!!!!!!!!!! Worked Great....

 

[Mikeyis4dcats]

I want to do just the opposite. I have a sheet with hundreds of phone numbers and contact info to import into Outlook. I need to convert ########## to ###-###-####. Ideas?

 

[xlbo]

either create a custom format of ###-###-####
OR
use
=left(A1,3)&"-"&mid(A1,4,3)&"-"&right(A1,4)

where your number is in A1

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244