Question on converting numbers into formatted text with Excel.
November 7, 2007 11:58 AM Subscribe
Question on converting numbers into formatted text with Excel.
I would like to take a column of numbers in Excel, which are formatted like (123) 456-7890, but which are actually stored in Excel as 1234567890 with the format Number | Custom | (###) ###-#### , and be able to merge them into Word such that upon merging, the numbers show up as (123) 456-7890, not 1234567890. Word's merge brings them in as numeric values, not formatted text.
Is there any way to convert these numbers to text and let them keep the parentheses, space, and hyphen? Workarounds in either Excel or Word are welcome.
I have a 800 row spreadsheet with multiple similar number rows that eventually need to be brought into an InDesign doc with linked story fields, so you can see why I really don't want to do this by hand.
I would like to take a column of numbers in Excel, which are formatted like (123) 456-7890, but which are actually stored in Excel as 1234567890 with the format Number | Custom | (###) ###-#### , and be able to merge them into Word such that upon merging, the numbers show up as (123) 456-7890, not 1234567890. Word's merge brings them in as numeric values, not formatted text.
Is there any way to convert these numbers to text and let them keep the parentheses, space, and hyphen? Workarounds in either Excel or Word are welcome.
I have a 800 row spreadsheet with multiple similar number rows that eventually need to be brought into an InDesign doc with linked story fields, so you can see why I really don't want to do this by hand.
Best answer: The formula
=TEXT(cell,"(###)###-####")
should do what you want.
posted by Wolfdog at 12:10 PM on November 7, 2007 [1 favorite]
=TEXT(cell,"(###)###-####")
should do what you want.
posted by Wolfdog at 12:10 PM on November 7, 2007 [1 favorite]
Best answer: Quick and dirty workaround...
Suppose your numbers are in Column A.
In column B, starting at B1 use: =TEXT(A1,"(###) ###-####")
You might be able to merge using column B, but if not, you can then copy column B and Paste Special>Values in to Column C... Column C is now all in plaintext so merge should work...
posted by saintsguy at 12:11 PM on November 7, 2007
Suppose your numbers are in Column A.
In column B, starting at B1 use: =TEXT(A1,"(###) ###-####")
You might be able to merge using column B, but if not, you can then copy column B and Paste Special>Values in to Column C... Column C is now all in plaintext so merge should work...
posted by saintsguy at 12:11 PM on November 7, 2007
Response by poster: Beautiful - you'd think that the online or offline help functions would have mentioned that you can use those masks with TEXT but I sure as hell didn't see it. Thank you!
posted by Optimus Chyme at 12:14 PM on November 7, 2007
posted by Optimus Chyme at 12:14 PM on November 7, 2007
Best answer: Just for future reference, you can also format the merge field in the Word document to do the same thing, without having to fiddle with the spreadsheet/database. You do it by using a numeric picture field switch (\#) within the merge field.
Once you have created your merge document in Word, you will have several merge fields that look like <>>, where your data will go. Right-click on the phone field and select "Toggle Field Codes". The field will now change to look something like this:
{MERGEFIELD phone} *
To add the field switch, just type it directly into the merge field itself. So to change the format of your plain numbers, you would add \# "'('###') '###'-'####" (the field switch, space, double quotes at each end, a single quote around text elements like the parenthesis+space and hyphens). The final merge field would look like this:
{MERGEFIELD phone \# "'('###') '###'-'####"}
Once you have entered the text, you right-click again and select "Update Field" to make it go back to normal. Voila, you have the correctly formatted numbers.
(The field code switches are really powerful, as you can do all kind of neat "if x then y" nested conditions for the output when you merge something. For example, only displaying the country field in an address if the address is outside the U.S. Really useful to learn if you do a lot of mail merging.)
* - Note that you can't just type the {} in, you have to create them with the insert mail merge field function.
posted by gemmy at 10:14 PM on November 7, 2007 [2 favorites]
Once you have created your merge document in Word, you will have several merge fields that look like <>>, where your data will go. Right-click on the phone field and select "Toggle Field Codes". The field will now change to look something like this:
{MERGEFIELD phone} *
To add the field switch, just type it directly into the merge field itself. So to change the format of your plain numbers, you would add \# "'('###') '###'-'####" (the field switch, space, double quotes at each end, a single quote around text elements like the parenthesis+space and hyphens). The final merge field would look like this:
{MERGEFIELD phone \# "'('###') '###'-'####"}
Once you have entered the text, you right-click again and select "Update Field" to make it go back to normal. Voila, you have the correctly formatted numbers.
(The field code switches are really powerful, as you can do all kind of neat "if x then y" nested conditions for the output when you merge something. For example, only displaying the country field in an address if the address is outside the U.S. Really useful to learn if you do a lot of mail merging.)
* - Note that you can't just type the {} in, you have to create them with the insert mail merge field function.
posted by gemmy at 10:14 PM on November 7, 2007 [2 favorites]
This thread is closed to new comments.
posted by coevals at 12:10 PM on November 7, 2007