text to columns: split at the first number in the

I have 1 column with about 60 cells with values or different length. Each (or at least most) of the values have a numeric characters in the value. I want to split the columns cells into more columns which I normally would do with the 'tekst to columns' function of excel.

But this function does not have an advanced option of splitting the value at the first numeric character. splitting based on spaces, comma etc. is possible but this does not help me.

Is there any way to divide the cells into 2 columns at the first number in the cell value?

I have looked at numerous other questions but none of them (or other internet fora) have helped me to split the value at the first number of the cell value.

Thanks #quantum285 for the answer. This routine works if the string contains one number. I changed the teststring to firstpart323secondpart. then part1 returns 'firstpart32' and part2 return secondpart.

I tried to understand what happens in this code, please correct me if I'm wrong:

First, the lenght of the string is determined.

Secondly, for each position in this string is checked if it is numeric or not. But this check is dan from right to left? So in case of firstpart323secondpart: the length is 22.
then isnumeric() checks for every position from 1 to 22 if it is numeric and stops when it finds a number?

If so, part 1 is the the tekst before the value i, where i is the first position from right to left in the string where a number is found.

and part 2 is then the string on the right from this same position.

However, I am looking for a routine which find the first position from left to right (or the last position from right to left) where a number is, ...

So I changed the routine now, simply adjusting the for i = 1 to line:

Sub test()
For j = 4 To Cells(Rows.Count, 4).End(xlUp).Row
For i = Len(Cells(j, 4)) To 1 Step -1
    If IsNumeric(Mid(Cells(j, 4), i, 1)) Then
        Cells(j, 5) = Left(Cells(j, 4), i - 1)
        Cells(j, 6) = (Right(Cells(j, 4), Len(Cells(j, 4)) - i + 1))
    End If
Next i
Next j

End Sub

this almost perfectly works (except for a few cells which have multiple number combinations in the value (like: soup 10g 20boxes). But as these are only a few, I can adjust them by hand.

Thanks!

Sub test()
testString = "firstpart3secondpart"
For i = 1 To Len(testString)
    If IsNumeric(Mid(testString, i, 1)) Then
        part1 = Left(testString, i - 1)
        part2 = (Right(testString, Len(testString) - i))
    End If
Next i
MsgBox (part1)
MsgBox (part2)
End Sub

Use something like this within your loop.