Concatenate with line breaks

jongooligan

Board Regular
Joined
Jul 23, 2007
Messages
90
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Paste the following codes in the macro window ( Alt F11)

Code:
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
=a1&" "&char(10)&b1

format cells to wrap text (on the alignment tab)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,446
Office Version
  1. 365
Platform
  1. Windows
=a1&" "&char(10)&b1

format cells to wrap text (on the alignment tab)
=A1&CHAR(10)&B1 should do the same job?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.
 

Forum statistics

Threads
1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top