# Combine Text With Carriage Returns

• I have been trying to figure this out and just can’t seem to get it. I am hoping someone out there can help.

I have 4 cells with text in them that I am trying to combine into one cell and not have any blank lines between the text.

The cells are arranged like this:
A1="One" B1=Cell where the text is combined
A2="Two"
A3="Three"
A4="Four"

I set the cell alignment in B1 to wrap text and use the following formula in B1:
=IF(A1=””,””,A1)&IF(A2=””,””,CHAR(10)&A2)&IF(A3=””,””,CHAR(10)&A3)&IF(A4=””,””,CHAR(10)&A4)

The problem is if there isn’t anything in one of the cells in column A it makes a blank line between the text in column B. I am trying to get the text to the top of B1 and not have any blank lines between the lines of text.

Is there a way to change the formula so that it will do the following three things:
1. If the cell A1 doesn’t have anything in it then don’t put the value in B1.
2. If A1 has something in it and A2 has something in it then put the text of A1 in B1 and HAVE a carriage return after the A1 text.
3. If A1 has something in it and A2 does not have anything in it then put the text of A1 in B1 but DO NOT have a carriage return after it.

• Re: Combine Text Cells With No Blank Lines Between Text

Andrew5,

Do you need for this to work on more than 4 lines?

G.

• Re: Combine Text Cells With No Blank Lines Between Text

Try,

=IF(LEN(A1),IF(AND(LEN(A1),LEN(A2)),A1&CHAR(10),IF(AND(LEN(A1),LEN(A2)=0),A1,"")),"")

HTH

• Re: Combine Text Cells With No Blank Lines Between Text

Quote from ByTheCringe2

Kris, I've noticed that you use
=IF(LEN(A2)...
so that if A2 contains a string of any length >0, it equates to TRUE. I've tested this and it works, but is this documented?

I'm not aware of.

For logical test we use..

=IF(ISNUMBER(...

=IF(ISTEXT(...

=IF(CELL="",...

=IF(CELL<>"",...

=IF(LEN(CELL),..

=IF(ISBLANK(....

etc.

The advantage of LEN function over ISNUMBER and ISTEXT is it can be used for both text and numeric entries.

• Re: Combine Text Cells With No Blank Lines Between Text

Thanks, Kris. I see we are both on Excel 2000. Could someone please check if it is valid in Excel 2003? I am a little concerned that it may be an unofficial usage that may be fixed in a later edition.

• Re: Combine Text Cells With No Blank Lines Between Text

Zero always evaluates to False and any number of above zero evaluates True. Been that way since.....

• Re: Combine Text With Carriage Returns

Oh! Thanks, Dave - that had escaped me since... : D

• Re: Combine Text With Carriage Returns

I just finished testing out the formula that Krishnakumar had posted and it works exactly like I needed it to and I learned something new with how to set up if statements.

You guys are the best!!! Thanks for a such a great helpsite!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!