If cell B2 is not blank, "length" + information of cell B2 should be shown

Hello there,

I have an excel sheet that contains product details. So for example in cell B2 I have the length of a product, C2 the width, D2 the height and E2 the material. I wish to make an 'automatic product text' on the basis of these cells, but some cells are left blank (because information is missing).
So what I should have, ideally is the following result:

- Length: B2
- Width: C2
- Height: E2
- Material: D2

But for example if cell E2 is blank, this should not be shown in the text.

I tried the formula
=(IF(B2<>"";"-Length";""))

which shows the text -Length (and nothing when B2 is blank), but how can I add the information of cell B2 to this text? And how can I continue the formula so that the rest of the information is filled in as well?

Thank you in advance

Best wishes,

Brecht

See if this formula in cell F2 and copied down will give you the desired description:

="Length: "&IF(B2>0,B2,"unavailable, ")&" Width: "&IF(C2>0,C2,"unavailable, ")&" Height: " &IF(E2>0,E2," unavailable, " )&" "&D2

@KenDue thank you for your reply.



So yes, the formula works, but what I want is that for Product 2 (where the width is unavailable), the text becomes: Length: 1; Height: 3; Material: Metal .

Try this
=IF(B2>0,"Length: " & B2,"")&IF(C2>0,"; Width: "&C2,"")&IF(D2>0,"; Height: "&D2,"")&IF(E2<>"","; Material: "&E1:E3,"")

I would agree with Sunny's changes until you get to the "Material: " segment of the formula. I don't see any need for the "IF" statement or the range E1:E3... just end the formula &"; Material: "&E2 (or whatever cell contains the description of the material.

Hi KenDue, thanks for your comment.

I felt it is better to be safe to check the material as well. The 2 sample data given may not cover all the scenarios.

Try this file