We help IT Professionals succeed at work.

How to merge cells based on contents of cells

Dave
Dave asked
on
Hello
I would like to merge the contents of cells based on whether the cells contain any data.  Please see attached file.  I would like to end up with the results as in column h, but with the formula below I only end up with one set of data in the h column. Any help is appreciated.
Thanks

=IF(B2<>"",$B$1&" - "&B2&" | ",IF(C2<>"",$C$1&" - "&C2&" | ",IF(D2<>"",$D$1&" - "&D2&" | ",IF(E2<>"",$E$1&" - "&E2&" | ",IF(F10<>"",$F$1&" - "&F10&" | ",IF(G10<>"",$G$1&" - "&G10&" | ",""))))))
test.xlsx
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2012

Commented:
Try:

=IF(B2<>"",$B$1&" - "&B2&" | ","")&IF(C2<>"",$C$1&" - "&C2&" | ","")&IF(D2<>"",$D$1&" - "&D2&" | ","")&IF(E2<>"",$E$1&" - "&E2&" | ","")&IF(F2<>"",$F$1&" - "&F2&" | ","")&IF(G2<>"",$G$1&" - "&G2&" | ","")

and copy down.

See attached,

Cheers,

Dave
test.xlsx
Most Valuable Expert 2012
Top Expert 2012
Commented:
To eliminate the trailing | at the very end:
=IF(B5<>"",$B$1&" - "&B5&IF(C5&D5&E5&F5&G5<>""," | ",""),"")&IF(C5<>"",$C$1&" - "&C5&IF(D5&E5&F5&G5<>""," | ",""),"")&IF(D5<>"",$D$1&" - "&D5&IF(E5&F5&G5<>""," | ",""),"")&IF(E5<>"",$E$1&" - "&E5&IF(F5&G5<>""," | ",""),"")&IF(F5<>"",$F$1&" - "&F5&IF(G5<>""," | ",""),"")&IF(G5<>"",$G$1&" - "&G5,"")

and copy down.

See attached:

Dave
test.xlsx

Author

Commented:
That did it - thanks a bunch Dave!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.