入黎免費幫你砌excel表 / 教你formula (4)

Sub ExtractNum()
  Dim i, j As Integer
  Dim a, b() As String

  i = 2
  a = ""

  Do While Not IsEmpty(Cells(i, 1))
    For j = 1 To Len(Cells(i, 1))
      If IsNumeric(Mid(Cells(i, 1), j, 1)) Then
        a = a & Mid(Cells(i, 1), j, 1)
      ElseIf j > 1 Then
        If IsNumeric(Mid(Cells(i, 1), j - 1, 1)) Then
          a = a & " "
        End If
      End If
    Next j

    b = Split(a)

    For j = 0 To UBound(b)
      Cells(i, 2 + j) = b(j)
    Next j

    i = i + 1
    a = ""
  Loop
End Sub