Saturday, May 10, 2008

How to Merge With Formatting from Excel to Word

Have you ever had a specially formatted value in an Excel field you are trying to merge into a Word document or merge to email but the formatting won't come with the data? So a formula like =SUM(B2:B16) is formatted so the result will show as currency in the cell but all you get in your merge result is 4893. Or you have a percentage that you format as percentage to two decimal places but you get 12.467633847898736 in your merge? I looked for a fix for this for a long time before I came across a forum (that I can't find now, sorry) that laid it out simply. Wrap your formula in a text command that specifies the formatting to give it. Sounds ugly, but it's really as easy as it looks:
will get the currency format to show up in the merge document. A colleague needed a phone number to show up properly and this is what worked:
=TEXT(E2,"(000) 000-0000") where cell E2 holds the raw phone number (9176567321).

No comments :