Split column text to adjacent columns using Excel

Question

I have an Excel sheet with a column containing texts like "Hello there 2005 A" I want to split this text in between two columns, one containing 'Hello there 2005' and the other saying 'A'.

I have tried Split function in VBA, but I can't make it loop through the entire column or even come up with a delimeter which will split exactly before the letter 'A'.

Results should look something like this:

Answer 1

Instr(cellValue," ") will give you the position of your first space

firstPos = instr(cellValue," ")  ' first space
secondPos = instr(firstPos + 1, cellValue, " ") ' second space

etc..

or

followed by mid, and replace

secondColumnValue = mid(cellValue, thirdPos + 1)
firstColumnValue = replace(cellValue, secondColumnValue, "")

Answer 2

try this

Option Explicit

Sub main()
Dim cell As Range
Dim strng As Variant
Dim rightStrng As String
Dim i As Long

With Worksheets("multimanager") '<== change it as per your needs
    For Each cell In .Columns("A").SpecialCells(xlCellTypeConstants, xlTextValues) 'assuming that "column containing texts" is column "A"
        strng = Split(cell)
        rightStrng = ""
        i = UBound(strng)
        Do While Not IsNumeric(strng(i)) And i > 0
            rightStrng = strng(i) & " " & rightStrng
            i = i - 1
        Loop
        If IsNumeric(strng(i)) Then
            rightStrng = Application.WorksheetFunction.Trim(rightStrng)
            cell.Offset(, 2) = rightStrng
            cell.Offset(, 1) = Left(cell.Value, IIf(rightStrng <> "", InStrRev(cell.Value, rightStrng) - 2, Len(cell.Value)))
        End If
    Next cell

End With

End Sub