display USA phone format in formula bar

[Stephen S]

Excel 2003
I have lists of phone numbers in continuous 10 digits format. I'd like to
convert them to USA format so I can import them (with area code in
parentheses etc) into a PTA school phone directory using Word 2003 merge. I
know about Format/Cells/Special/Phone Number but this is obviously only for
display and printing purposes. While the cells show the US format, the
unformatted 10 digits are still in the formula bar.

 

[FSt1]

hi stephen,
you are right. formatting only changes the way data looks. it DOES NOT
change the data. So how do you change the data.
try this formula....
="(" & MID(B20,1,3) & ")" & MID(B20,4,3) & "-" & MID(B20,7,10)
this will add the parentheses and dash. copy the formula down. after copy
the column and Edit>PasteSpecial>values. this will turn the formula in to
hard text.

Regards
FSt1

 

[David McRitchie]

Hi Stephen,
You could use a macro to permanently change the content of a selection
or the entire sheet for that matter to what you see. Basically:
cell.value = cell.text

Convert to the Text Values (#convert_to_text)
http://www.mvps.org/dmcritchie/excel/join.htm#convert_to_text

Test with a copy of your sheet, to make sure it works for you in Word
before deciding if to permanently change your worksheet. I personally
would only use text for phone numbers and zip codes.

 

[Stephen S]

Thanks David and FST1.
I went to the Word discussion group and there are field switches in Mail
Merge which also do the conversion.

Hi Stephen,
You could use a macro to permanently change the content of a selection
or the entire sheet for that matter to what you see. Basically:
cell.value = cell.text

Convert to the Text Values (#convert_to_text)
http://www.mvps.org/dmcritchie/excel/join.htm#convert_to_text

Test with a copy of your sheet, to make sure it works for you in Word
before deciding if to permanently change your worksheet. I personally
would only use text for phone numbers and zip codes.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Excel 2003
I have lists of phone numbers in continuous 10 digits format. I'd like to
convert them to USA format so I can import them (with area code in
parentheses etc) into a PTA school phone directory using Word 2003 merge. I
know about Format/Cells/Special/Phone Number but this is obviously only for
display and printing purposes. While the cells show the US format, the
unformatted 10 digits are still in the formula bar.

 

[Marge]

Excel 2003
I have lists of phone numbers in continuous 10 digits format. I'd like to
convert them to USA format so I can import them (with area code in
parentheses etc) into a PTA school phone directory using Word 2003 merge. I
know about Format/Cells/Special/Phone Number but this is obviously only for
display and printing purposes. While the cells show the US format, the
unformatted 10 digits are still in the formula bar.

 

[Marge]

You can rearrange almost any data by using a combination or mid and
concatenate functions. Your equation would look something like this
=MID(D27,1,3)&"-"&MID(D27,4,3)&"-"&MID(D27,5,4) when original 10 digit phone
number is in cell D27

 

[James Silverton]

Marge wrote on Sun, 10 Feb 2008 11:11:00 -0800:

M> "Stephen S" wrote:

??>> Excel 2003
??>> I have lists of phone numbers in continuous 10 digits
??>> format. I'd like to convert them to USA format so I can
??>> import them (with area code in parentheses etc) into a PTA
??>> school phone directory using Word 2003 merge. I know about
??>> Format/Cells/Special/Phone Number but this is obviously
??>> only for display and printing purposes. While the cells
??>> show the US format, the unformatted 10 digits are still in
??>> the formula bar.

This is not meant critically and I understand that you mean
(123) 456-7890 as USA Format (as does Excel) but a lot of
places use 123-456-7890 or even 123.456.7890. I use the
hyphenated form myself and sometimes add the preceding "1" still
necessary to keep some phone companies happy even if, like me, a
lot of people have unlimited calling, thus 1-123-456-7890. I
discovered quite recently that my version of Profile will take
123-456-7890 but insists on storing and displaying later as
(123) 456-7890. However, if I use 1-123-456-7890, it is stored
and displayed as is.

Unlike San Francisco, around my part of the US, the so-called
area code is always dialed and cannot be assumed. Leave it off
and you get the "Are you mentally deficient" sounding recording:
"If you need assistance, please call an operator".

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not