Combining single rows into multiple lined items
I have an Excel 2003 spreadsheet containing several thousand rows of data which looks like the attached image.
The goal is to print out these addresses on Avery 1 x 4" labels using an Avery template with Word 2003.
The end result for each address would look like this:
FIRST NAME LAST NAME
ADDRESS LINE 1
ADDRESS LINE 2 (if any)
CITY, STATE, ZIP
My first idea was to concatenate the data together in another cell and running through all of the cells this way.
I was trying something like this:
But it didn't work - the CHAR(13) space character does not work and the formula seemed to break.
Can anyone think of a clever way to do this? I have a rudimentary knowledge of VBA and I'll welcome any ideas.
Also, I guess when I'm done formatting everything in Excel I can just copy and paste the entire new concatenated column into Word (right?).
Last edited by bread; 08-31-2009 at 12:28 PM.
Re: Combining single rows into multiple lined items
I think I figured out a formula for it:
=A1&" "&B1&CHAR(10)&C1&CHAR(10)&D1&CHAR(10)&IF(ISBLANK(E1),"",E1&CHAR(10))&F1&", "&G1&CHAR(32)&H1
(This formula takes into consideration an additional column not in my original screenshot). The IF conditional part removes an unnecessary line break if the second address line is blank.
It seems to work for what I need it to do.
Now I just need to work on seeing if there's a good way to get all of the data onto the labels in Word.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1