+ Reply to Thread
Results 1 to 16 of 16

Intentional blank spaces being removed during mail merge

  1. #1
    Registered User
    Join Date
    03-14-2012
    Location
    US
    MS-Off Ver
    Excel 2007, Windows 7
    Posts
    9

    Intentional blank spaces being removed during mail merge

    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!!!

  2. #2
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,860

    Re: Intentional blank spaces being removed during mail merge

    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

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,443

    Re: Intentional blank spaces being removed during mail merge

    Hi,

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    seems to work as expected.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,860

    Re: Intentional blank spaces being removed during mail merge

    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

  5. #5
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,582

    Re: Intentional blank spaces being removed during mail merge

    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.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  6. #6
    Registered User
    Join Date
    03-14-2012
    Location
    US
    MS-Off Ver
    Excel 2007, Windows 7
    Posts
    9

    Re: Intentional blank spaces being removed during mail merge

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    seems to work as expected.
    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.
    Last edited by birdablaze; 12-22-2014 at 02:53 PM.

  7. #7
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,582

    Re: Intentional blank spaces being removed during mail merge

    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.
    Last edited by FDibbins; 12-22-2014 at 05:39 PM. Reason: modified some wording

  8. #8
    Registered User
    Join Date
    03-14-2012
    Location
    US
    MS-Off Ver
    Excel 2007, Windows 7
    Posts
    9

    Re: Intentional blank spaces being removed during mail merge

    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.
    Last edited by FDibbins; 12-22-2014 at 05:40 PM. Reason: modified some wording

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,498

    Re: Intentional blank spaces being removed during mail merge

    You can use 160 (nonbreaking spaces) vs code(129)
    Ben Van Johnson

  10. #10
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,582

    Re: Intentional blank spaces being removed during mail merge

    Quote Originally Posted by birdablaze View Post
    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.
    Quote Originally Posted by birdablaze View Post
    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...

  11. #11
    Registered User
    Join Date
    03-14-2012
    Location
    US
    MS-Off Ver
    Excel 2007, Windows 7
    Posts
    9

    Re: Intentional blank spaces being removed during mail merge

    Quote Originally Posted by macropod View Post
    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.

  12. #12
    Registered User
    Join Date
    03-14-2012
    Location
    US
    MS-Off Ver
    Excel 2007, Windows 7
    Posts
    9

    Re: Intentional blank spaces being removed during mail merge

    Quote Originally Posted by protonLeah View Post
    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.

  13. #13
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,582

    Re: Intentional blank spaces being removed during mail merge

    Quote Originally Posted by birdablaze View Post
    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).

  14. #14
    Registered User
    Join Date
    03-14-2012
    Location
    US
    MS-Off Ver
    Excel 2007, Windows 7
    Posts
    9

    Re: Intentional blank spaces being removed during mail merge

    Quote Originally Posted by macropod View Post
    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.

    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.

  15. #15
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,582

    Re: Intentional blank spaces being removed during mail merge

    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.

  16. #16
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,860

    Re: Intentional blank spaces being removed during mail merge

    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
    Attached Files Attached Files
    Last edited by LJMetzger; 12-23-2014 at 10:43 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 06-24-2014, 01:34 PM
  2. [SOLVED] Transpose Mail Merge (Row to Column) based on Blank Cell
    By jfsportz in forum Excel General
    Replies: 3
    Last Post: 08-19-2012, 07:09 AM
  3. Replies: 2
    Last Post: 07-12-2012, 08:11 PM
  4. How to remove spaces in mail merge
    By caustin in forum Excel General
    Replies: 2
    Last Post: 06-07-2006, 05:20 PM
  5. Replies: 1
    Last Post: 03-15-2005, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1