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>>
Riny_van_Eekelen That's exactly what I want to do but unfortunately my database is chopped up into columns that don't fit into the mail merge program. I tried to attach the headings to my last message but it didn't work so I'll just type it:
A B C D E F G H I j
DeliveryDate/Phone/FirstName/LastName/StreetNum/StreetName/StreetType/UnitType/UnitNum/ /
K
Zipcode
those are the column headings. There are only 2 lines on mail merge for address so I tried to combine the 5 columns like this: =CONCAT(E2, F2, G2, H2, I2) Then I got the #NAME? in the extra column that I inserted - J. What am I doing wrong?
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
