Question on converting numbers into formatted text with Excel.

November 7, 2007 11:58 AM

Question on converting numbers into formatted text with Excel.

I would like to take a column of numbers in Excel, which are formatted like [phone], 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 [phone], 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.

posted by Optimus Chyme to computers & internet (5 answers total)

In Excel, click on the column containing the numbers, go to the Format menu, and select Cells...then you will get a dialog box where you can set the category as text. (using Office 2004 on a Mac, so ymmv.)
posted by coevals at 12:10 PM on November 7, 2007

The formula
=TEXT(cell,"(###)###-####")
should do what you want.
posted by Wolfdog at 12:10 PM on November 7, 2007 [1 favorite]

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

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

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]