Adding a DASH in the middle of my field

[steven.allman]

I download a mail merge file sometimes 15 times a day for a form we have. The mailmerge contains SSN's in the 123456789 format, but I want to make the Word Document display in the 123-45-6789 format. I can edit the source each and every time, but if there is an way to code that in, using * wildcards or something in SQL, I would really love to make this happen

[macropod]

Hi Steven,

You can add a numeric picture switch to your SSN mergefield. To do this:
1. Select the SSN mergefield and press Shift-F9. It should look something like {MERGEFIELD SSN}, where 'SSN' is the field name.
2. Delete anything after the field name.
3. After the field name, add the switch ' \# "000'-'00'-'0000"', so that you end up with {MERGEFIELD SSN \# "000'-'00'-'0000"}.
4. Press F9 to update the field.
Your mailmerge is now ready for use.

[steven.allman]

Perfection, worked like a charm!!!
If you know that code, perhaps you can help me with another.

The address field in the packet I recieve is doubly annoying.

"SMALLTOWN IN456781234"
(forum will not let me add the 10-12 spaces between smalltown and IN)
I have to edit it to show

"SMALLTOWN, IN 45678-1234"

Of course I cannot control the source of my packet information, I just have to edit each one manually. These two steps add the most annoying 30 seconds to each one. Without that I can create one of these in seconds..

Also, how can I drop anything after a space " " in a merge field.
Field information has "BLAH 1234"
I want the field to show me "BLAH" in my word document and nothing else.
Any Ideas???

[macropod]

Hi Steven,

I cannot control the source of my packet information, I just have to edit each one manually.

If you're getting a plain text file, it probably wouldn't take too much work to run it through a series of Find/Replace operations in Word to clean it up and maybe add some more field delimiters before using it for the mailmerge. That way, you could get rid of any unwanted spaces and, if you want, have separate fields for the locality, state & zip, so that each can be formatted and positioned however you want them and independently of each other. If the data are consistent enough, the process could be encoded in a macro that does everything from opening the data file, cleaning it up, saving the updates, then executing the mailmerge. The whole process could be almost as quick as doing the mailmerge now after you've cleaned the data.

Re:

how can I drop anything after a space " " in a merge field

If the field contains only numbers, that's easy enough to do via a formual field coded along the lines of:
{={MERGEFIELD SomeField} \# 0}
That will even strip leading 0s - or you can add extra 0s to the one in the field to preserve them. Coding the field as:
{={MERGEFIELD SomeField} \# "$,0"}
will even format it as currency with thousands separators.

However, there is no practical way for trimming for text with field coding, so you can't turn "BLAH 1234" into "BLAH". Pre-processing in Word would enable you to place the strings into separate fields, though.

[steven.allman]

It is a plain text, but it is always just a single record, so 'find/replace' would actually take longer than editing source from within the Mail Merge step by step. However, the idea of splitting the fields sounds intriguing. Could I not split the fields, and then eliminate a one of the new fields?

[macropod]

Originally Posted by steven.allman

It is a plain text, but it is always just a single record, so 'find/replace' would actually take longer than editing source from within the Mail Merge step by step.

Not if it's built into a macro that does the cleanup work then executes the merge for you.

However, the idea of splitting the fields sounds intriguing. Could I not split the fields, and then eliminate a one of the new fields?

Yes, you could do that, or you could leave in in the data and simply not use it.