CONCATENATE with a Carriage Return? Can you?

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi All:

I did a quick search but did not fine what I was looking for (too many results).

I need to combine some data that is currently in 2 columns (G and H). The problem I am having is trying to get a carriage return in there so that the info from Column H goes below the info from Column G. I placed a carriage return in J and tried this:

=CONCATENATE(G7 & J7 & H7)

The info went together but all on one line. Is what I am asking for possible and if so how? These are all address listing like this:

G7 = "4000 Sheppard Avenue East"
H7 = "Scarborough, Ontario M1S 4X4

I need my result shown as:

4000 Sheppard Avenue East
Scarborough, Ontario
M1S 4X4

Any suggestions?

THANKS,
Mark
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Tip 1. If you're going to use the &, you might as well drop the CONCATENATE...

it's lke saying SUM(SUM(A1,B1))
=CONCATENATE(G7 & J7 & H7)
Same As
=A7 & J7 & H7

2.
use CHAR(10) for a carriage return
=A7 & CHAR(10) & J7 & CHAR(10) & H7

You'll have to turn on Word Wrap
Format - Cells - Alignment
Wrap Text
 
Upvote 0
THANKS to BOTH of you for your help. I did not know what to use for a Carriage Return. You have both indicated CHAR(10) and I was just wondering if this and other codes are listed in Excel or is this just something that IT type people know. If it is listed maybe there are other things of interest there as well..?

Regardless, you guys have me up and running.

Have a GREAT day and THANKS Again...
Mark:biggrin:
 
Upvote 0
You can easily get a displayed list of them all by doing this on an available column

=CHAR(ROW())
copy down 255 rows.
 
Upvote 0
THANKS AGAIN to BOTH of you. :biggrin:

I will check out the Posted List and try and create one myself

Take Care,
Mark :biggrin:
 
Upvote 0
When I paste the the concatenated output from a cell with char(10) into a pdf it removes all the carriage returns and puts the whole thing in quotes. Any ideas how to fix this?
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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
Back
Top