如何在excel中将非空单元格用“,”连接
发布网友
发布时间：2024-10-02 03:09
共4个回答
热心网友
时间：2024-11-12 19:38
我发现这个问题很好玩啊，如果参数多了，比如5个，10个，如果只用公式来做，是相当复杂的一件事啊。
如果用两个单元格来写，还能写的开公式，如果想用一个，字符数量要发疯！
我自己试验了一下，可以弄出来15个单元格的你要的效果，如果分成两个单元格，公式分别是：
O1里是：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1&","&G1&","&H1&","&I1&","&J1&","&K1&","&L1&","&M1&","&N1,",,,,,",","),",,,",","),",,",",")
P1里是：=IF(LEFT(IF(RIGHT(O1,1)=",",LEFT(O1,LEN(O1)-1),O1),1)=",",SUBSTITUTE(IF(RIGHT(O1,1)=",",LEFT(O1,LEN(O1)-1),O1),",","",1),IF(RIGHT(O1,1)=",",LEFT(O1,LEN(O1)-1),O1))
如果合并成一个。。。看图
如果用宏，那就很简单了
Sub aaa()
For i = 1 To 15
If Not Cells(1, i).Value = "" Then
c = c & Cells(1, i).Value & ","
End If
Next
c = Left(c, Len(c) - 1)
Cells(1, 16).Value = c
End Sub
追问额~ 请教下 如何在1-200行执行这个宏？ 或者说同时处理200行数据追答如果你只要5个格的，这个公式吧
=LEFT(IF(A1="","",A1&",")&IF(B1="","",B1&",")&IF(C1="","",C1&",")&IF(D1="","",D1&",")&IF(E1="","",E1&","),LEN(IF(A1="","",A1&",")&IF(B1="","",B1&",")&IF(C1="","",C1&",")&IF(D1="","",D1&",")&IF(E1="","",E1&","))-1)
下面是能循环的宏
Sub aaa()
For r = 1 To 200 '能修改从第几行开始，要多少行的
c = ""
For i = 1 To 15 '能修改从第几列开始，到多少列（即横着几个单元格）
If Not Cells(r, i).Value = "" Then
c = c & Cells(r, i).Value & ","
End If
Next
If Len(c) > 0 Then
c = Left(c, Len(c) - 1)
End If
Cells(r, 16).Value = c
Next
End Sub
热心网友
时间：2024-11-12 19:39
=A1&IF(B1="","","，")&B1&IF(C1="","","，")&C1&IF(D1="","","，")&D1&IF(E1="","","，")&E1追问这个公式可以确保在A1不为空的情况下得到正确答案，不过空格位置是随机的
追答失败
热心网友
时间：2024-11-12 19:39
" , " 不要中间那个逗号
" " 中间输入一个或多个空格
热心网友
时间：2024-11-12 19:40
