We help IT Professionals succeed at work.

# How to merge cells based on contents of cells

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

## View Solution Only

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

Commented:
That did it - thanks a bunch Dave!