Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-08-2011, 07:26 AM
steven.allman steven.allman is offline Adding a DASH in the middle of my field Windows Vista Adding a DASH in the middle of my field Office 2010 32bit
Novice
Adding a DASH in the middle of my field
 
Join Date: Apr 2011
Posts: 3
steven.allman is on a distinguished road
Default Adding a DASH in the middle of my field

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
Reply With Quote
  #2  
Old 04-08-2011, 05:49 PM
macropod's Avatar
macropod macropod is offline Adding a DASH in the middle of my field Windows 7 32bit Adding a DASH in the middle of my field Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-11-2011, 04:46 AM
steven.allman steven.allman is offline Adding a DASH in the middle of my field Windows Vista Adding a DASH in the middle of my field Office 2010 32bit
Novice
Adding a DASH in the middle of my field
 
Join Date: Apr 2011
Posts: 3
steven.allman is on a distinguished road
Default

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???
Reply With Quote
  #4  
Old 04-11-2011, 05:38 AM
macropod's Avatar
macropod macropod is offline Adding a DASH in the middle of my field Windows 7 32bit Adding a DASH in the middle of my field Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Steven,

Quote:
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:
Quote:
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-11-2011, 06:03 AM
steven.allman steven.allman is offline Adding a DASH in the middle of my field Windows Vista Adding a DASH in the middle of my field Office 2010 32bit
Novice
Adding a DASH in the middle of my field
 
Join Date: Apr 2011
Posts: 3
steven.allman is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 04-11-2011, 02:24 PM
macropod's Avatar
macropod macropod is offline Adding a DASH in the middle of my field Windows 7 32bit Adding a DASH in the middle of my field Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by steven.allman View Post
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.
Quote:
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to refer a field in another field DaveSmith Word 0 08-24-2010 06:44 PM
Dash in the beginning of a new line Wakinyan Word 0 07-11-2010 07:10 AM
Page break middle of table (word 2003) mujisan Word Tables 1 06-18-2010 12:05 AM
Adding a field to a folder of PDF files. Balliol Windows 0 11-22-2009 02:02 PM
Hyphen vs. en-dash tocuin Word 0 09-18-2009 10:01 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft