How do I concatenate \ combine multiple cells into a single cell

[IainD]

I have hundreds of lines of addresses, with the address currently spread over five cells horizontally, for example:

Line 1 of the address in Cell A1
Line 2 of the address in Cell B1
Line 3 of the address in Cell C1
Line 4 of the address in Cell D1
Line 5 of the address in Cell E1

Is there a simple macro I could use\create, to copy the five horizontal cells, into a single cell, so that the address appear in the following format, within the cell:

Address line 1
Address line 2
Address line 3
Address line 4
Address line 5

As opposed to the format shown below:

Address line 1 Address line 2 Address line 3 Address line 4 Address line 5


Any help would be very much appreciated, as to complete this reformat by hand will take hours, if not days.

Kind regards,

Iain.

 

[jmthompson]

Can you post a sample of your data?

 

[IainD]

Appologies.

I can not see how to attach an excel spreadsheet to a posting & can not paste the cells.

Essentially I can to merge the 5 cells worth of data into one cell:

=A1&B1&C1&D1&E1

In excel if you are in a cell between two word & pess "ALT & Retrun", you two pieces of data appear on their own lines within the cell.

I want to take the merged cells =A1&B1&C1&D1&E1 adding the "ALT & Retrun", within the =A1&B1&C1&D1&E1, something along the lines of:

Does anyone know the macro code for "ALT & Retrun"?

Kind regards,

Iain.

 

[jmthompson]

it's vbnewline, but what I want to see is how the individual addresses are separated

 

[IainD]

Thanks for the reply.

Each part of the address is in it's own cell.

Is there a chr() for "vbnewline" that can be used in a =A1&B1 to create a new line within a cell?

Are you able to advise how to upload part of an excel spread sheet?

Your help is much appriciated.

Kind regards,

Iain.

 

[jmthompson]

look at the 4th sticky on this board.

The code would include something like
=A1 & vbnewline & B1 & vbnewline & C1 & vbnewline & D1 & vbnewline & E1

 

[IainD]

I have tried the code, but get the error message

#name?

 

[IainD]

The data in Excel is as shown below.

I am trying to move it into a single cell, with part on its own line, seperating it with "Alt return". I have tried the recommended:

=A1 & vbnewline & B1 & vbnewline & C1 & vbnewline & D1 & vbnewline & E1

But get the error #name?

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>ADDADD1</th><th>ADDADD2</th><th>ADDADD3</th><th>ADDADD4</th><th>ADDADD5</th></tr> <tr><td>Balcan Engineering Ltd.</td><td>Banovallum Court</td><td>Boston Road Industrial Estate</td><td>Horncastle,</td><td>Lincolnshire</td></tr> <tr><td>BSS (UK) Lewis</td><td>Unit 5, Southdowns Business Park</td><td>Brooks Road</td><td>Lewes</td><td>East Sussex</td></tr> <tr><td>CANNING ROAD</td><td>SOUTHPORT</td><td>MERSEYSIDE</td><td> </td><td> </td></tr></table>

 

[jmthompson]

Try this (assumes no header)

Sub AddBlock()
Dim lastRow As Long
Dim c As Range

lastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("F1:F" & lastRow)
If c.Offset(, -5) <> "" Then c = c.Offset(, -5).Value & Chr(10) & c.Offset(, -4).Value & Chr(10) & c.Offset(, -3).Value & Chr(10) & c.Offset(, -2).Value & Chr(10) & c.Offset(, -1).Value
Next c
End Sub

 

[IainD]

That's absolutly brilliant, thanks for taking the time to help.

Kind regards,

Iain.