Carriage returns in a concatenate formula ?

**SimonL** · 10-26-2010, 03:22 PM

Hi
I have searched but to no avail, I hope I am not duplicating a previously answered question.

I want to use a concatenate formula to merge the contents of three columns, these are address fields, into a single column. I have done this using

Please Login or Register  to view this content.

But I'd like to have a carriage return between the three values so C2 Carriage return D2 Carriage return E2

So I end up with the data appearing like this in the single cell within the concatenated column:

Address line 1
Address line 2
Address line 3

Is this possible ?
I really hope so, because I have a spreadsheet with just under 9,800 entries and this would save me days of heart ache.

Many thanks in advance for any help you can give.
Regards
Simon

**NBVC** · 10-26-2010, 03:25 PM

Try:

=CONCATENATE(C2,CHAR(10),D2,CHAR(10),E2)

then format with Wrap Text.

**SimonL** · 10-27-2010, 05:07 AM

Thank you !
This would appear to be the solution.
Many thanks

Simon

**SimonL** · 10-27-2010, 06:04 AM

Hummm, so I have three columns, that I have concatenated beautifully thanks to your formula.

But sometimes there is no value in one of the columns, but we have programmed the formula to insert a carriage return. Obviously sometimes this is redundant and it would be fantastic if we could insert a clause to say

'if there isn't a value in that cell, don't bother with the carriage return on that occasion'

Is this possible ?

Thanks.

Simon.

**DonkeyOte** · 10-27-2010, 06:11 AM

Perhaps:

Please Login or Register  to view this content.

**SimonL** · 10-27-2010, 07:53 AM

Amazing !
You are a God amongst men :-)

The amount of hours work you have just saved me. I had 10,000 rows to edit and if you hadn't helped with this formula it looked like I would have had to manually adjust them all.

Thank you so much !

Regards
Simon

**NBVC** · 10-27-2010, 08:02 AM

Here's another way...

=CONCATENATE(C2,IF(D2="","",CHAR(10)),D2,IF(E2="","",CHAR(10)),E2)

**gerrig20** · 04-02-2013, 04:59 PM

Hi All,
I am trying to do almost the same thing, except that I am concatenating two cells that have multiple entries already:

Cell A Cell B
74 176
180 179

I would like to end up with all values in one cell:
74
180
176
179

What I am getting is this:
74
180176
179

I would greatly appreciate any help with this. I have about 2900 rows with various values to contend with.

Thank you so much,
G.

**arlu1201** · 04-03-2013, 01:31 AM

gerrig20,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.