try and __thoroughly test__ the following macro
Code:

Sub blah()
Dim Result(1 To 7)
For Each cll In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Erase Result
xx = Split(cll.Value)
For j = 1 To Len(xx(0))
If Not IsNumeric(Left(xx(0), j)) Then
Result(1) = Left(xx(0), j - 1)
Result(2) = Mid(xx(0), j)
Exit For
End If
Next j
If Left(xx(1), 2) Like "C#" Then
If Mid(xx(1), 2, 1) < 8 Then
Result(3) = Left(xx(1), 2)
xx(1) = Mid(xx(1), 3)
End If
End If
If Left(xx(1), 2) Like "#y" Then
Result(4) = Left(xx(1), 2)
xx(1) = Mid(xx(1), 3)
End If
If Right(xx(1), 2) Like "G#" Then
If Right(xx(1), 1) < 4 Then
Result(6) = Right(xx(1), 2)
xx(1) = Left(xx(1), Len(xx(1)) - 2)
End If
End If
If Len(xx(1)) > 0 Then Result(5) = xx(1)
Result(7) = Split(xx(2), "K")(0)
cll.Offset(, 1).Resize(, 7) = Result
Next cll
End Sub

My trial sheet is set up thus:

| A | B | C | D | E | F | G | H |

1 | DATA SOURCE | | | | | | | |

2 | 7GF C5Hc 4K | 7 | GF | C5 | | Hc | | 4 |

3 | 7Gd C43yHc 6K | 7 | Gd | C4 | 3y | Hc | | 6 |

4 | 9St C5Md 21K | 9 | St | C5 | | Md | | 21 |

5 | 7St/Slw C43yHc 4K | 7 | St/Slw | C4 | 3y | Hc | | 4 |

6 | 12St/Fs C5Md 2K | 12 | St/Fs | C5 | | Md | | 2 |

7 | 16Y/Sft HcH 815K | 16 | Y/Sft | | | HcH | | 815 |

8 | 17Sft Ch 4K | 17 | Sft | | | Ch | | 4 |

9 | 24Gd HcCh 5K | 24 | Gd | | | HcCh | | 5 |

10 | 22Hy NvChG3 23K | 22 | Hy | | | NvCh | G3 | 23 |

11 | 7GS C12yG3 22K | 7 | GS | C1 | 2y | | G3 | 22 |

Excel tables to the web >> Excel Jeanie HTML 4

with the results appearing in columns B:H. The results match you required results bar the value in cell H2 which I think you got wrong.

## Like this thread? Share it with others