CONCATENATE with CHAR(10) IF NOT ISBLANK

[Sisilla]

Hello All,

Can anyone tell me why this formula will not work? I do not get any
error messages. The formula is displayed in the cell as its value.

=CONCATENATE(OFFSET(RC, 0, 0), IF(ISBLANK(OFFSET(RC, 0, 4)), "",
CHAR(10)), OFFSET(RC, 0, 4), IF(ISBLANK(OFFSET(RC, 0, 8)), "",
CHAR(10)), OFFSET(RC, 0, 8))


I appreciate any effort to help me. Thank you for your time and
consideration.

Sincerely,

Sisilla

 

[JE McGimpsey]

The only problem I see is that, when entered in a cell, using

OFFSET(RC, 0, 0)

creates a circular reference. I also wonder why you're using the
overhead of OFFSET() rather than

=CONCATENATE(RC, IF(ISBLANK(RC[4]), "", CHAR(10)), RC[4],
IF(ISBLANK(RC[8]), "", CHAR(10)), RC[8])

 

[Gord Dibben]

Good chance that the cell with the formula showing was pre-formatted as text.

Format to General and hit F2 then ENTER.

If all formulas are showing then try hitting the toggle key for "show formulas"

CTRL + ` backquote above Tab key


Gord Dibben MS Excel MVP

 

[Sisilla]

Thank you, Gord and JE both. I've corrected the circular reference and
reformatted the cell, and now I am getting a formula error. Can you
spot anything else wrong with my formula? I appreciate any further
advice. Thanks!
-Sisilla

 

[Sisilla]

Thank you, Gord and JE both. I've reformatted the cell and fixed the
circular reference, and now I get an error message. Can you spot
anything else wrong with the formula? I appreciate any further advice.
Thanks! -Sisilla

 

[JE McGimpsey]

Can you be more specific about your "formula error"?

 

[Sisilla]

Thank you JE and Gord both. I fixed the circular reference and
reformatted the cell, and now I get a formula error message. Can you
spot anything else wrong with the formula? I appreciate any further
advice. Thanks!
-Sisilla