Mail Merge Format Problem

[Evil8]

I have a office 2003 spreadsheet with a social security number column. It is formated as special - social security number so when adding numbers you type them in as 123456789 and it looks like 123-45-6789. Now when merging the data into a word document it does not put the field in with the proper formating. It looks like 123456789. The problem is we are not typing up the spreadsheets we get that data from someone else and retyping it is not an option.

Thanks for your help!

 

[GSCaupling]

Evil8: I think the solution will involve formatting on the Word side, not the source. I played with it briefly and the following produced "123 45 6789" but I couldn't get the dashes to appear. If spaces will suffice, the following works; otherwise, we're only a little closer.

Right click on the merge field and select Toggle Field Codes. Then add a formatting switch such as: {MERGEFIELD F2 [blue]\# 000-00-0000[/blue]}.

Good luck.

 

[SkipVought]

Hi,

Although GSCaupling's solution may work for you, there is a more fundamental issue to consider: The Difference between NUMBERS and IDENTIFIERS.

Numbers are usually values that you will do arithmetic on. Leading ZEROS are irrelevant. The number of digits in the value is usually irrelevant. Numbers can be negative.

Identifiers, on the other hand, although they may contain only numeric characters, are values that you will not use in mathematical calculations. The number of characters is very significant and the value will NEVER be negative.

Values such as phone numbers, social security numbers, Zip codes, invoice numbers, employee numbers etc, are used exclusively for IDENTIFICATION and have no numeric significance other than for collation.

Furthermore, integral numeric values have upper limits, based on data type, the value of which could limit desired values.

Also data that mixes types (character & numeric, like 5 AND 9 digit Zip codes, do not give consistent results when doing lookups and queries.

So as a matter of course, I make ALL my identifiers TEXT, by prefixing a ' AND inserting dashes explicitly when required.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Evil8]

I found a solution. You can format merge fields in the Word Document by typing alt+f9 and changing the field to look like:

{Mergefield Social_Security_Number \# "000'-'00'-'0000"}

alt+f9 gets you back to the regular merge field view.

 

[GSCaupling]

Evil8 - thanks for posting your solution. I had tried "-" around the dashes, but not the single quotes. Good work.