请教各位老师,有什么函数可以快速做成下图“合并”列那样格式,谢谢。目前我用CONCATENATE,但由于编号1-4的数字组的不相连,(不知道这样表达有人明白吗)所以最后我还是需要手动改G列的CONCATENATE公式,比较麻烦。
A | B | C | D | E | F | G | 名称 | 编号1 | 编号2 | 编号3 | 编号4 | 合并 | , | 1 | | 810099994692 | 810099994807 | | 810099994692,810099994807 | =CONCATENATE(C3,$G$2,D3) | 2 | | 810099994692 | 810099994807 | | 810099994692,810099994807 | =CONCATENATE(C4,$G$2,D4) | 3 | 810099993125 | 810099994692 | | | 810099993125,810099994692 | =CONCATENATE(C5,$G$2,B5) | 4 | 810099993125 | 810099994692 | | | 810099993125,810099994692 | =CONCATENATE(C6,$G$2,B6) | 5 | 810099993125 | 810099994692 | | | 810099993125,810099994692 | =CONCATENATE(C7,$G$2,B7) | 6 | 810099993125 | 810099994692 | | 810099996811 | 810099993125,810099994692,810099996811 | =CONCATENATE(C8,$G$2,B8) | 7 | 810099993125 | 810099994692 | 810099994807 | | 810099993125,810099994692,810099994807 | =CONCATENATE(C9,$G$2,B9) | 8 | 810099993125 | 810099994692 | 810099994807 | | 810099993125,810099994692,810099994807 | =CONCATENATE(C10,$G$2,B10) | 9 | 810099993125 | 810099994692 | 810099994807 | | 810099993125,810099994692,810099994807 | =CONCATENATE(C11,$G$2,B11) | 10 | 810099993125 | 810099994692 | 810099994807 | | 810099993125,810099994692,810099994807 | =CONCATENATE(C12,$G$2,B12) | 11 | 810099993125 | 810099994692 | 810099994807 | | 810099993125,810099994692,810099994807 | =CONCATENATE(C13,$G$2,B13) | 12 | 810099993125 | 810099994692 | 810099994807 | | 810099993125,810099994692,810099994807 | =CONCATENATE(C14,$G$2,B14) | 13 | 810099993125 | 810099994692 | 810099994807 | | 810099993125,810099994692,810099994807 | =CONCATENATE(C15,$G$2,B15) | 14 | 810099993125 | 810099994692 | 810099994807 | | 810099993125,810099994692,810099994807 | =CONCATENATE(C16,$G$2,B16) | 15 | 810099993125 | 810099994692 | 810099994807 | | 810099993125,810099994692,810099994807 | =CONCATENATE(C17,$G$2,B17) | 16 | 810099993125 | | 810099994807 | | 810099993125,810099994807 | =CONCATENATE(D18,$G$2,B18) | 17 | 810099993125 | | 810099994807 | | 810099993125,810099994807 | =CONCATENATE(D19,$G$2,B19) | 18 | 810099993125 | | 810099994807 | | 810099993125,810099994807 | =CONCATENATE(D20,$G$2,B20) | 19 | 810099993125 | | 810099994807 | | 810099993125,810099994807 | =CONCATENATE(D21,$G$2,B21) |
yasmine16 发表于 2014-5-16 16:14
OK,我把编号换了一下,要求也像上面所说一样合并 ,请各位老师指教,谢谢。
excel对文本处理是弱项 - =IF(B2<>"",B2&",","")&IF(C2<>"",C2&",","")&IF(D2<>"",D2&",","")&E2
复制代码
|