CONCATENATE with CHAR(10) IF NOT ISBLANK

S

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
 
J

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])
 
G

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
 
S

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
 
S

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top