Concatenate with line breaks

[jongooligan]

I've inherited a workbook with data like:

A1 = 1 The Street B1 = Anytown

In C1 if I use A1&" "&B1 I get 1 The Street Anytown

What I need in C1 is:

1 The Street
Anytown

Other info - The example shows just two of seven address lines that need to be concatenated into one cell. There are approximately 200 addresses to be manipulated.

Thanks

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11)

Sub sss()
x = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
For b = 1 To 7
Cells(a, 8) = Cells(a, 8) & Cells(a, b) & Chr(10)
Next b
Next a
End Sub

run the macro

 

[PaddyD]

=a1&" "&char(10)&b1

format cells to wrap text (on the alignment tab)

 

[Peter_SSs]

=a1&" "&char(10)&b1

format cells to wrap text (on the alignment tab)

=A1&CHAR(10)&B1 should do the same job?

 

[PaddyD]

yep - formally, the extra space is entirely gratuitous. functionally, though, resultant strings are easier to read (imo) when, for whatever reason, wrap text format hasn't been set, & has no adverse effects when it has.