Split cells into 2 columns when any number

Question

I can see numerous posts around this topic but none that specifically solves the problem I have.

I have a string that has text and numbers. I need to split the string into 2 columns when it first sees a number.

Example:

Ballyvic Boru5/6
First Drift2/1
Sizing Cusimanoin15/2

Becomes:

Answer 1

You can use a simple formula to find the first number, along with LEFT and MID to split the string.

Part 1:

=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890"))-1)

Part 2:

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890")),99)

Answer 2

Here's a regex method:

You must set a reference to Microsoft VBScript Regular Expressions x.x, where x.x is the highest version you have (mine is 5.5)

Option Explicit

Sub splitCells()

    Dim RegEx As New RegExp, i As Long, tempStr As String

    With RegEx
        .Global = True
        .IgnoreCase = True
        .Pattern = "(([a-z]*\s?)*\D)(\d.*)"
    End With

    With ThisWorkbook.Worksheets(1)

        For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row

            If RegEx.Test(.Cells(i, 1)) Then
                tempStr = .Cells(i, 1)
                .Cells(i, 1) = RegEx.Replace(tempStr, "$1")
                .Cells(i, 2) = RegEx.Replace(tempStr, "$3")
           End If

        Next i

    End With

End Sub

---

Breaking down the Regular Expression:

(([a-z]*\s?)*\D)(\d.*)

[a-z]* matches any character in the alphabet, with the * multiplier for unlimited occurances

\s? Matches any whitespace character, with the ? multiplier to match 0-1 occurances (meaning there may or may not be a white space

Both of the above is enclosed in a grouping (), followed by another * to match 0-unlimited occurances

\D This excludes all digits

The above is enclosed in a group with the first (([..])*\D)

We have our final group: (\d.*), which matches the first digit and everything else afterwards.