Formatting Phone Numbers in Excel 2007 Help

M

MrMike

I have phone numbers in two different formats:
1. 1-123-456-7890 (11 digits w/dashes)
2. 1234567890 (10 digits only)

How can I convert the first format into the standard format in excel for
phone numbers of (###) ###-####?

I know the second format will do it automatically.

Thanks in advance for your help.
 
P

pmartglass

You can do a replace


ctrl + h

seek for -
leave replace blank

it will strip the area you select from all hyphens and then you can format
the same as the other data
 
M

MrMike

This removed the dashes, however I still have an 11 digit phone number with a
1 at the beginning, which I also would like to remove. Is there a better way
to do both? Meaning remove the dashes and the 1 before the number?
 
M

MrMike

This is also a good start, which is more of what I'm looking for, however I
now have a 4 digit area code because of the on before the number. Is there a
way to do this and remove just the 1 before the before number? All the other
functions I've tried removes all the ones in the phone number which is not
what I want to do.
 
P

pmartglass

in thinking more about it, it would need to look like this

=TEXT(RIGHT(TEXT(SUBSTITUTE(B8,"-",""),"#"),10),"(###)-###-####")

hope this helps
 
D

David Biddulph

Isn't it as simple as changing
=TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
to
=TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?

Why wouldn't that work?
 
M

MrMike

Yes, this is the one I used, thanks.



David Biddulph said:
Isn't it as simple as changing
=TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
to
=TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?

Why wouldn't that work?
--
David Biddulph




.
 
M

MrMike

Thanks for your help and direction, I used
=TEXT(RIGHT(TEXT(SUBSTITUTE(C2,"-",""),"#"),10),"(###) ###-####")
and just removed the extra - between the area code paranthesis and the
prefix of the phone number.
 
M

MrMike

One more thing, is there anyway to save this in excel so I can get to it
quickly without having to refer to any notes?
 
G

Gord Dibben

Save what?

Just the formula for future use?

I keep a workbook handy where I stick all such stuff with an explanation of
what it does.

Several worksheets.......Indirect, Sumproduct, Vlookup

Copy the formula and paste it.

Precede it with an apostrophe so's it is visible as text.

I keep that workbook always open for testing.

I also store macros and code in several modules named appropriately in an
add-in which is always loaded for testing code.


Gord Dibben MS Excel MVP
 
B

Bob Phillips

I use the code librarian that came with Excel 2000 Developer, but I always
have trouble re-installing it when I rebuild, so I am thinking of going with
KeyNote.
 
M

MrMike

Bob,
I don't see a code librarian in Excel 2007 under the Developer Tab. Did
they remove it or should I look somewhere else for it?
Thanks
 
B

Bob Phillips

No you won't, it was an extra that came with the Excel 2000 Developer
edition (which I still have).
 
B

Bob Phillips

No it can't. I did contact MS and suggested that they make it available as a
free download. My contact said he would enquire, but nothing has happened.


Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top