#  Other Applications & Softwares  > Word Formatting & General >  >  Intentional blank spaces being removed during mail merge

## birdablaze

I have an excel spreadsheet with two columns. The first column (A) has names and the second column (B) has a formula [=LEFT(A2&REPT(" ",26),26)] to add spaces after the name to reach a max character count of 26. The reason for this formula is because I have to put this information into a fixed-character-width text document. 

Unfortunately, when I attempt to mail merge column B, the intentional blank spaces are removed and this screws up my character count. I want the cell to merge exactly as it appears in excel. 

I've tried with no results:
- changing both columns to "text" 
- opening the data source as MS Excel Worksheets via DDE

I hope this is possible but I'm scared it is not. If not, perhaps someone can help me figure out a way to achieve my ultimate goal which would require additional explanation. 

Thank you!!!

----------


## LJMetzger

Hi  birdablaze,

You might want to try a slightly different approach.  

a. Instead of adding spaces to your Excel file, add a printing character that would NEVER appear in your word document such as a tilde (~) or a reverse apostraphe (`).
b. If you are doing a manual mail merge do a universal replace to replace the 'space replacement character' with actual spaces in your merged word file.
c. If you have automated mail merge using Excel, try code like the following (tested and working using an automated mail merge):



```
Please Login or Register  to view this content.
```


For additional automated mail merge information using Excel, see posts #4 and #9 in the following thread: http://www.excelforum.com/excel-prog...merge-doc.html

Lewis

----------


## Richard Buttrey

Hi,

I tried Lewis's excellen suggestion of ~ or ` characters but in my test word doc they were showing. I did however find that



```
Please Login or Register  to view this content.
```



seems to work as expected.

----------


## LJMetzger

I intentionally used characters such as tilde (~) that could be seen.  My intention was the character had to be replaced by a space after the Mail Merge was completed.  I was afraid there would be unintended consequences if a non-printing character were used (with no replacement).

Lewis

----------


## macropod

If you're trying to maintain a particular visual layout in Word, you really shouldn't be padding the content out with spaces anyway; use tabs & tab-stops or a table in the mailmerge main document - that's what they're there for. Using spaces to pad content out will only work reliably with fixed-width fonts (e.g. Courier New) and treats Word like it's a typewriter - which it isn't.

----------


## birdablaze

> Hi,
> 
> I tried Lewis's excellen suggestion of ~ or ` characters but in my test word doc they were showing. I did however find that
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> ...



This works almost perfect. I get the fixed width that I would like but when I view the paragraph and formatting options, there aren't actual blank spaces in the document. Please see this image: http://i.imgur.com/fgRy0JY.jpg

As you can see the dots are not showing up after the name so when I copy and paste into Notepad or save as a plain text document (which is the type of document I have to upload), the formatting is all screwy. 

I will try the other suggestions when I have more time later.

Edit: I'm attempting to create something that my coworkers can easily replicate without getting too deep into formatting. If I seem amateur, you have no idea how much of an expert I am compared to other around me.  :Smilie:

----------


## macropod

IMHO, expecting your coworkers to quite unnecessarily pad out the source data with ~, then to replace the ~ with spaces after doing a mailmerge just because _you_ don't want to do some basic formatting of the mailmerge main document is a little short-sighted. After all, if you took the few moments that are required to do the proper setup of your mailmerge main document, your coworkers wouldn't have to do anything other than run the mailmerge. Not only are you an amateur (not that there's anything wrong with that), you're one with a bee in their bonnet about doing things a particular way instead of doing it properly. I'm sure your coworkers would be less than impressed to learn that there is a better way to do this.

----------


## birdablaze

What basic formatting do you think I'm unwilling to do?

I'm not familiar with VBA or the coding someone else suggested and planned on trying it at home later.  You cant expect me to implement something I've never even seen before. 

I'm trying to save my coworkers hours of monotony filling out an online form when the entire project could be competed in five minutes if I can make this mail merge work. No hoops. Just simple copying and pasting.

----------


## protonLeah

You can use 160 (nonbreaking spaces) vs code(129)

----------


## macropod

> What basic formatting do you think I'm unwilling to do?



You've made it quite plain you're unwilling to use something as simple as a tab & tab-stop.




> I'm not familiar with VBA or the coding someone else suggested and planned on trying it at home later.  You cant expect me to implement something I've never even seen before.



VBA has nothing to do with this and I certainly haven't suggested it. It is completely unnecessary _if_ you do things properly...

----------


## birdablaze

> You've made it quite plain you're unwilling to use something as simple as a tab & tab-stop.
> 
> VBA has nothing to do with this and I certainly haven't suggested it. It is completely unnecessary _if_ you do things properly...



Perhaps you can elaborate on tab stops if you feel that may be the best approach.  I've only used tab stops when manually formatting a document. 

I may not be explaining myself correctly. I'm not attempting to create a specific visual layout. I'm attempting to create a simple text document that meets these criteria: http://i.imgur.com/62tx3Jy.png.

----------


## birdablaze

> You can use 160 (nonbreaking spaces) vs code(129)



Unfortunately, this did not work for me.  The spaces appeared in the excel document after the last name and I was able to copy and paste that cell into Word with the additional spaces.  But the spaces were lost completely during merge.

----------


## macropod

> I may not be explaining myself correctly. I'm not attempting to create a specific visual layout. I'm attempting to create a simple text document that meets these criteria: http://i.imgur.com/62tx3Jy.png.



OK. I've had a look at your image. Since what you're after is a padded text file, why are you using Word & mailmerge at all? Why not simply save the data that way directly from Excel? If you pad the data appropriately, then save the worksheet as a tab-delimited or plain text file, all you'll need to do afterwards (using nothing more sophisticated that NotePad, if you wish) is to delete the tab-stops (and add 'EOF' if you need that).

----------


## birdablaze

> OK. I've had a look at your image. Since what you're after is a padded text file, why are you using Word & mailmerge at all? Why not simply save the data that way directly from Excel? If you pad the data appropriately, then save the worksheet as a tab-delimited or plain text file, all you'll need to do afterwards (using nothing more sophisticated that NotePad, if you wish) is to delete the tab-stops (and add 'EOF' if you need that).



 
Basically, I go into my system and mark accounts. All of the data from the marked account is transferred into a spreadsheet. It's like a hundred columns wide with all kinds of information that I do not need. I only need the social, last name, and today's date, all of which are columns on the spreadsheet. When I tried to save as a plain text file (and everything in between), it pulls tons of columns that I do not need and which do not meet the character-width requirement. And I don't know how to get rid of that stuff in notepad.  I cannot expect my coworkers to fiddle with columns and stuff in excel.  Shoot, asking them to insert a new column, paste a formula and pull down to apply to all rows is already a daunting task in and of itself.

The mail-merge process is familiar to the staff and that is why I think it may be the best method. Everything works great except merging the one column that has extra spaces. 

Is there perhaps an IF statement I can nest in the mail merge that says something like [If LASTNAME < 100 characters “[26 blank spaces]”-count(LASTNAME)]. That would be a dream come true.  :Smilie: 

Like I said, I'm very amateur and don't have a lot of time to dedicate to teaching myself more complicated methods. I've already dedicated way too much energy to figuring this out and it's only because our current process takes a ridiculous amount of soul-crushing time.

----------


## macropod

So why not either delete the columns you don't want or, simpler still, set up a new sheet with a single formula to pull in and pad just the columns you do want, with all the output going to a single column on the new sheet - a trivial exercise. You really are making this far more complicated than it needs to be. The formula to do all that would be something like:
=LEFT(Sheet1!A2&REPT(" ",9),9)&LEFT(Sheet1!B2&REPT(" ",8),8)&LEFT(Sheet1!C2&REPT(" ",26),26)&LEFT(Sheet1!D2&REPT(" ",20),20)&LEFT(Sheet1!E2&REPT(" ",28),28)&LEFT(Sheet1!F2&REPT(" ",8),8)&LEFT(Sheet1!G2&REPT(" ",20),20)
Simply edit the sheet & columns reference to suit. Of course, for a complete neophyte, a macro that does the equivalent of inserting the formula, then copying the output to a text file that it saves would be the simplest solution of all. If you asked for help on that front - and provided the relevant details as to which columns the data come from and where & with what named the text file is to be saved - I'm sure someone would be more than willing to help.

And no, a mailmerge _cannot_ test string lengths for padding purposes.

----------


## LJMetzger

birdablaze,

Please try the attached files, that perform an automatic mail merge, and will substitute an ASCII space in the final word document for all of the following characters:
a. tilde (~)
b. reverse apostrophe (`)
c. decimal 129
d. decimal 160

The files included in the zip file are:
a. LJMOpenWordMailMerge4.xls - file that does all the work (contains the macros).
b. LJMMailMerge.doc - demo Word file that contains mail merge fields.
c. LJMMailMergeData4.xls - demo Excel file that contains mail merge data.

Instructions for use of the demo program.
a. Unzip all 3 files into the same folder.
b. Open file LJMOpenWordMailMerge4.xls (with Macros enabled).
c. 'Left Click' the Top Right Yellow Shape to automatically insert file names b. and c. above into the PALE GREEN fields.
d. 'Left Click' the Top Left Yellow Shape to run the Mail Merge.
e. Select 'Cancel' when asked to save the 'Word' file, and examine the contents of the Word file.

To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
http://office.microsoft.com/en-us/tr...001150634.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

The Excel mail merge code follows:



```
Please Login or Register  to view this content.
```


Lewis

----------

