单元格内字符串拆分

liangmutou01

    本来是在求助中发现的一个问题，但没记住位置，一时又找不到了，只好发帖，分享给大家，贴中有人用函数的方法解决，这只能解决第一行，否则每一行的公式都要变化才行，我用vba全自动解决。

原要求

vba代码：
Sub caifen()
  Dim yh As Long, yzhs As Long, xh As Long, nr, cd As Integer, ccd As Integer
  Range("f2:g10000") = ""
  yzhs = Range("a10000").End(xlUp).Row
  If yzhs = 1 Then
    MsgBox "没有需要拆分的数据。"
    Exit Sub
  End If
  For yh = 2 To yzhs
    nr = Split(Cells(yh, 2), "/")
    cd = UBound(nr)
    If cd = -1 Then
      MsgBox "具体拆分第" & Str(Cells(yh, 2)) & "行时出错。"
      Range("f2:g10000") = ""
      Exit Sub
    End If
    For ccd = 0 To cd
      xh = Range("g10000").End(xlUp).Row + 1
      Cells(xh, 7) = nr(ccd)
      Cells(xh, 6) = Cells(yh, 1)
    Next ccd
    ReDim nr(0)
  Next yh
End Sub
file:///C:\Users\Administrator\AppData\Roaming\Tencent\Users\22972251\QQ\WinTemp\RichOle\ETI2UA_ZENVRL)GCBM}_ICG.png

file:///C:\Users\Administrator\AppData\Roaming\Tencent\Users\22972251\QQ\WinTemp\RichOle\ETI2UA_ZENVRL)GCBM}_ICG.png

hcm19522

C2:C5=LEN(B2)-LEN(SUBSTITUTE(B2,"/",))+1
E2:E14=IF(ROW(A1)>SUM(C$2:C$5),"",OFFSET(A$2,SUMPRODUCT((ROW(A1)>SUMIF(OFFSET(C$2,,,{1;2;3;4}),">0"))*1),))
F2:F14=IF(E2="","",TRIM(MID(SUBSTITUTE(INDEX(B:B,MATCH(E2,A:A,)),"/",REPT(" ",99)),COUNTIF(E$2:E2,E2)*99-98,99)))