Concatenation after checking column contains any text or not

Hi Everyone!

I have a query related to concatenating several columns.

I have to make a product category path by concatenating categories from columns. each product is different and have different no. of categories, some has 1 category while others have 3, some have 4.

It will come up like this: Health & Beauty > Health Care > Biometric Monitor Accessories > Blood Glucose Meter Accessories

Above example there are 4 categories which are concatenate with ">".

Now, I know the concatenation formula, BUT I can only apply once I have a check whether category has value or not..

Looking forward to some guidelines in creating such formula.

Thanks,

Hamza

Try these
=IF(A2="","",IF(B2="",A2,A2&">"&B2&IF(C2="",">"&C2,">"&C2&">"&D2)))
where A2 as the 1st. category, B2 the 2nd., ... and D2 only exist if c2 exist, C2 only exist if B2 exist ...

I hope this helps

Thank you for your help, I am now trying to make it for all 7 categories I have but unable to do so, there are A2, B2, C2, D2, E2, F2 and G2. If you can kindly assist I would be grateful.

Regards,

Hamza

Thank you for your help, I am now trying to make it for all 7 categories I have but unable to do so, there are A2, B2, C2, D2, E2, F2 and G2. If you can kindly assist I would be grateful.

Regards,

Hamza

Originally Posted by José Augusto

Try these
=IF(A2="","",IF(B2="",A2,A2&">"&B2&IF(C2="",">"&C2,">"&C2&">"&D2)))
where A2 as the 1st. category, B2 the 2nd., ... and D2 only exist if c2 exist, C2 only exist if B2 exist ...

I hope this helps

Thanks for your help but it is not allowing me to drag the formula to the last row.

Originally Posted by Ace_XL

Try

=A2&SUBSTITUTE(B2,B2," > "&B2)&SUBSTITUTE(C2,C2," > "&C2)&SUBSTITUTE(D2,D2," > "&D2)&SUBSTITUTE(E2,E2," > "&E2)&SUBSTITUTE(F2,F2," > "&F2)&SUBSTITUTE(G2,G2," > "&G2)

THANKS A LOT - It worked now as I tried to save it..

Originally Posted by Ace_XL

Try

=A2&SUBSTITUTE(B2,B2," > "&B2)&SUBSTITUTE(C2,C2," > "&C2)&SUBSTITUTE(D2,D2," > "&D2)&SUBSTITUTE(E2,E2," > "&E2)&SUBSTITUTE(F2,F2," > "&F2)&SUBSTITUTE(G2,G2," > "&G2)

Originally Posted by hamzabinnaeem

Thank you for your help, I am now trying to make it for all 7 categories I have but unable to do so, there are A2, B2, C2, D2, E2, F2 and G2. If you can kindly assist I would be grateful.

Regards,

Hamza

Hi
Use the following formula (systematized)
=IF(A2="","",A2&IF(B2="","",">"&B2&IF(C2="","",">"&C2&IF(D2="","",">"&D2&IF(E2="","",">"&E2&IF(F2="","",">"&F2&IF(G2="","",">"&G2)))))))

Formula:

Please Login or Register  to view this content.

Regards