Remove Spaces between words in Mail Merge 1

[PaultheITGuy]

Hi,

I wonder if you can help. I have set up a mail merge using Publisher, with Excel as the data source. it's a simple address labels one.

I know how to get the merge to remove empty lines when fields are empty, but I cannot get it to remove empty spaces when there are more than one field on a line.

For example: The spreadsheet contains the following fields - "Title", "First name(s)" and "Surname". The merge in Publisher shows "<<Title>> <<First name(s)>> <<Surname>>" but if the recipient does not have a title or first name then it can come out like this: " John Smith", "Mr Smith".

How do I remove these pesky spaces?

Thanks in advance

PC

PS - Publisher and Excel 2003, Windows 2000. Ta.

 

[gbaughma]

After your mail merge, do a replace for ' ' (two spaces) replaced by ' ' (one space)

You may need to run it a few times, until it says "no matches found"



Just my 2¢
-Cole's Law: Shredded cabbage

--Greg

http://parallel.tzo.com

 

[rjs]

You have to use an IF field

{ IF { MERGEFIELD Title } = "" "" "{ MERGEFIELD Title } " }{ IF { MERGEFIELD FirstName } = "" "" "{ MERGEFIELD FirsName } " }

So if title is blank, add nothing, if not, title and space AFTER.


R.Sobelman

http://www.tracklogins.com

 

[WinblowsME]

Insert a column in Excel and name the field "Full Name." Fill down the column with the following formula:

=TRIM(SUBSTITUTE(CONCATENATE(A2, " ", B2, " ", C2), "  ", " "))

 

[gbaughma]

Nice responses, WinblowsME and rjs! Just goes to show there are always multiple ways of doing things.



Just my 2¢
-Cole's Law: Shredded cabbage

--Greg

http://parallel.tzo.com

 

[PaultheITGuy]

Thanks guys - I used the concatenate in Excel and copied the formula down the column (it never occurred to me to do changes at the data end - d'oh!!) and then set the field in Publisher to show only 'Nonblanks'

It worked a treat and should make it nice and easy for the 'non-technical' user I have set this up for.