Forum Discussion
Combining address columns with CONCAT function
My Excel list has 5 columns for addresses: streetnum, streetname, streetype, unittype, and unitnum. I need to print 70 labels from it. I tried to do a CONCAT method but I got #NAME? I'm using a PC with Microsoft 8.1
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>>
8 Replies
Paula_in_AK You don't really give a way much. What exactly have you tried? Can you share the formula using CONCAT, producing the #NAME! error.
But, considering that you do mention that you need to print labels from an Excel table, why not use Word's Mailmerge function to connect to a data set in Excel and print the labels from there?
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?
- When I pressed "enter" the format got messed up - the H I J column headings go down one line.
