Forum Discussion
Combining address columns with CONCAT function
Paula_in_AK Are you on Excel 2019 or later? If not, the use of CONCAT will cause a #NAME! error. Use CONCATENATE in stead:
Or, why not try it this way:
=E2&" "&F2&CHAR(10)&G2&" "&H2&" / "&I2
The &-signs connects cells together and allow you to insert separators, like a space, a line-feed (i.e. CHAR(10) or a slash (or whichever you prefer/need).
Make sure that to you set cells with line feeds to "Wrap Text" from the Alignment group on the Home ribbon. Not sure though what impact this will have Mail Merge. Never tried it. Having said that, I don't understand why you can't create an address block in Word/MM that would look something like this:
<<FirstName>> <<LastName>>
<<StreetNum>> <<StreetName>>
<<StreetType>> <<UnitType>> / <<UnitNum>>
Paula_in_AK Are you on Excel 2019 or later? If not, the use of CONCAT will cause a #NAME! error. Use CONCATENATE in stead:
Or, why not try it this way:
=E2&" "&F2&CHAR(10)&G2&" "&H2&" / "&I2
The &-signs connects cells together and allow you to insert separators, like a space, a line-feed (i.e. CHAR(10) or a slash (or whichever you prefer/need).
Make sure that to you set cells with line feeds to "Wrap Text" from the Alignment group on the Home ribbon. Not sure though what impact this will have Mail Merge. Never tried it. Having said that, I don't understand why you can't create an address block in Word/MM that would look something like this:
<<FirstName>> <<LastName>>
<<StreetNum>> <<StreetName>>
<<StreetType>> <<UnitType>> / <<UnitNum>>
- When I go to mail merge and put in the first 3 items it won't let me add another. On the match fields list, when I put in the street name it erases the street number. But the first suggestion works, so I'll go with that. Thanks.
Not sure how you're using MailMerge, but it is a LOT more flexible than you indicate. You could do whole custom letters with variable paragraphs based on conditions...there's no reason at all why it would be limiting your fields for address labels unless you're using some template that is itself rigid and protected.
Here's a Youtube video that purports to help. I've not viewed the whole, but it looks as if it highlights the flexible options.
https://www.youtube.com/watch?v=zSwI4mgEYtg
- I suppose that this is for 365 and I'm using Office Excel and Word 2016. I have always had trouble using this version to do mail merge. At this point I have managed to combine the 5 fields into one and also managed to get the first label looking right, but for some reason Word refused to load the rest of the labels. I can preview them one at a time but the sheet is blank except for the first one. What am I doing wrong?
