Sorting a sheet

[maza_minx]

Hi All,

I have a fairly unique request which i think might require VB. however. i am a complete rookie at VB and do not know its capabilities.

I have a sheet with about 1000 rows. and 4 columns.

It looks as follows.

A1 B1 C1 D1
Bob Smith 100 Test Dr Toronto ON L1L1X1

A2 B2 C2 D2
Bob Smith 200 Test Dr Toronto ON L1L1X2

etc.

etc.

I need something which will take these addresses and merge them together into one cell or even multiple cells in a seperate sheet into the following format:

Bob Smith
100 Test Dr
Toronto ON
L1X1X1

I need this so that I can organize my sheet to be able to print address labels which I need to stick onto envelopes.

Any suggestions or help would be greatly appreciated.

Thanks to all as always!
Alex

 

[maza_minx]

Would there maybe be a way to export this data into tables in Word?

 

[maza_minx]

Please help?

 

[Jim-in-I]

Assuming that your 4 columns of address data are in Sheet1, A1 thru D1 you can enter this formula in another Sheet.

=CONCATENATE(Sheet1!A1&CHAR(10),Sheet1!B1&CHAR(10),Sheet1!C1&CHAR(10),Sheet1!D1)

Make sure that the cell where you enter the formula is formatted for "Wrap Text".

I have not played around with the import to a Word table, but you can Copy this condensed column of data from Excel and Paste Special into Word as Formatted Text (RTF). Then highlight the text in Word and select:
Tables > Convert > Text to Table > # of Columns =1

That woked for me.

 

[Norie]

Why not just use Word mail merge with the Excel spreadsheet as the data source?