Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-30-2014, 01:26 AM
DHammer DHammer is offline Suppress comma in "city , stat zip" line when blank Windows 7 64bit Suppress comma in "city , stat zip" line when blank Office 2010 32bit
Novice
Suppress comma in "city , stat zip" line when blank
 
Join Date: May 2014
Posts: 1
DHammer is on a distinguished road
Default Suppress comma in "city , stat zip" line when blank

I am trying to use the Mail Merge in Word 2010 with an Excel 2010 spreadsheet to print a phone roster. Everything works fine except where for various reasons there in no city, state, or zip.



What I have in my mail merge document is:
<<Name>>
<<street>>
<<city>>, <<state>> <<Zip>>
<<Home TX>> / <<cell TX>>

<<Name>>
<<street>>
<<city>>, <<state>> <<Zip>>
<<Home TX>> / <<cell TX>>

<<Name>>
<<street>>
<<city>>, <<state>> <<Zip>>
<<Home TX>> / <<cell TX>>

<<Name>>
<<street>>
<<city>>, <<state>> <<Zip>>
<<Home TX>> / <<cell TX>>

In some of the "street" fields I have inserted the phrase "Withheld by Request" or "DECEASED" rather than a street address. The "City, State, and Zip" fields are then intentionally left blank in my spreadsheet.

What I get on the printout is :
John Doe
123 Any St.
Boston, MA xxxxx
(123) 456-7890 / (123) 123-4567

Jane Doe
DECEASED
,
/

Kelly Doe
Withheld by Request
,
/

Mike Doe
321 My Street
Sidney, NY xxxxx
(111) 222-3333 / (555) 666-7777

What I would like to have print out is the following:
John Doe
123 Any St.
Boston, MA xxxxx
(123) 456-7890 / (123) 123-4567

Jane Doe
DECEASED



Kelly Doe
Withheld by Request



Mike Doe
321 My Street
Sidney, NY xxxxx
(111) 222-3333 / (555) 666-7777

Where the ", " is NOT printed if the "city, state zip" fields are empty and the " / " does not print if one or both of the phone fields are blank.

Any or all suggestions would be appreciated. Right Now I am going through the generated document and manually having to delete or use the replace command to get rid of the unwanted characters.

Thanks!

Last edited by DHammer; 05-30-2014 at 01:40 AM. Reason: correct display error
Reply With Quote
  #2  
Old 05-30-2014, 02:43 AM
macropod's Avatar
macropod macropod is offline Suppress comma in &quot;city , stat zip&quot; line when blank Windows 7 32bit Suppress comma in &quot;city , stat zip&quot; line when blank Office 2010 32bit
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

For the comma:
Delete both the comma and the space following the «city» field.
Select the «city» field and press Shift-F9. You should see { MERGEFIELD city }. Change this to { MERGEFIELD city \f ", "} and press F9 to update.

For the slash:
Delete the slash and the spaces either side of it.
Where the slash was, press Ctrl-F9 to create another field, which will look like { }
Insert another pair of «Home_TX» and «cell_TX» fields between the braces, thus:
{ «Home_TX» «cell_TX» }
Select the new «cell_TX» field and press Ctrl-F9 to create another field. You should now have:
«Home_TX»{ «Home_TX» {«cell_TX»} }«cell_TX»
Edit the fields you've created so you get {IF«Home_TX» <> "" {IF«cell_TX» <> "" " / "}}
Press F9 to update.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Suppress comma in &quot;city , stat zip&quot; line when blank Word is displaying my "compatible" docs as blank! RobDuBois Word 3 01-20-2013 04:40 PM
Suppress comma in &quot;city , stat zip&quot; line when blank How can I paste cell "A1" contents to cell "B1" if cell "A1" is not blank? Learner7 Excel 1 04-25-2011 04:39 AM
"Inserting" 11x17 blank sheet into report mcjohn Word 2 01-06-2010 09:14 AM
"Intentionally left in Blank" Pages Manolo Word 2 04-30-2009 04:20 AM
Suppress comma in &quot;city , stat zip&quot; line when blank Suppress "do you want to save?" dialog on close rnstewart Excel 2 12-28-2005 04:30 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:48 PM.


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