Here's a UDF you could use:
Function NumSplit(s As String) As Variant
Dim i As Long
Dim nOut As Long
Dim asOut() As String
ReDim asOut(1 To Application.Caller.Count)
Do
i = i + 1
If i > Len(s) Then Exit Do
If Mid(s, i, 1) >= 0 And Mid(s, i, 1) <= "9" Then
nOut = nOut + 1
asOut(nOut) = Left(s, i)
s = Mid(s, i + 1)
i = 0
End If
Loop
NumSplit = asOut
End Function
... and an example of its usage:
-------A-------- --B--- --C-- -D-- -E-- F ---------------G---------------
1 xxxx1 xxxx1 B1:F1 and down: {=NumSplit(A1)}
2 yyy3yyyy1 yyy3 yyyy1
3 xyzxx1yxz4xzx1 xyzxx1 yxz4 xzx1
4 xxx2xxx2xxx2xxx2 xxx2 xxx2 xxx2 xxx2
To add it to the workbook:
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Close the VBE to return to Excel
It requires an array formula. So for the example above, select cells A1:F1, and enter the formula in the Formula Bar without the curly braces. Then hold down the Ctrl and Shift keys, then press Enter. The curly braces will appear. Then you can drag down the fill handle to copy the formula down.
Bookmarks