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.

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 EditPasteSpecialvalues. this will turn the formula in to
hard text.

Regards
FSt1

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

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

"Stephen S" wrote in message ...
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.

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

"David McRitchie" wrote:

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

"Stephen S" wrote in message ...
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.

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

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

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

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