Excel 2007 : Split formula in 2 lines in 1 cell

**Raen** · 09-22-2010, 11:26 AM

Hi there!

I want to know if it's possible to "split" a formula into 2 lines of text in one cell.

I know that by pressing Alt+Enter when writing text in one cell, a line break is done, making the text after the position where Alt+Enter was done go into a new line below the current one (always in the same cell).

I want to do the same thing but with a formula, making the formula's 1st part appear in the 1st text line of the cell, and the 2nd part in the cell's 2nd line.
Much like pressing Alt+Enter in the middle of the formula.

Here's the case:

http://img832.imageshack.us/img832/7444/productxlsx.png

C3's formula is:

=IF(ISBLANK(A3);"";"Catalog Code:   ") & A3 & IF(ISBLANK(B3);"";"Reference:   ") & B3

I can do a line break pressing Alt+Enter when editing the formula (between "... & A3" and "& IF(..." ), but it has no results - in the fx space the formula appears separated in 2 lines, but in the cell it has no effect.

The effect that I desire in C3 is:

Catalog Code:   123
Reference:   123-ABC

instead of:

Catalog Code:   123Reference:   123-ABC

Any help is appreciated

**Marcol** · 09-22-2010, 12:01 PM

Try this

=IF(ISBLANK(A3),"","Catalog Code:   ") & A3 &CHAR(10)& IF(ISBLANK(B3),"","Reference:   ") & B3

Then apply Wrap Text to the formula cell

Hope this helps

**Raen** · 09-22-2010, 12:10 PM

Didn't know that function.

It worked, thanks

**Elena123** · 10-16-2013, 11:34 AM

Hi,

I am trying to make it work as well, but it doesn't take effect - neither version..
my formula is as follows -

=CONCATENATE("update_job: ",A8,"
description: ","""",Q8,"""")

and although ALT Enter makes formula to split to 2 lines (as you see above)- actual result doesn't split. Char(10) doesn't have any effect either..

Any thoughts on this?

**Elena123** · 10-16-2013, 11:43 AM

as soon as I posted it - firgured it out... Cell format must be set to "wrap text" - forgot about this step..

**arlu1201** · 10-17-2013, 06:21 AM

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.