Split cells in several columns with Excel formulas

Question

I have a cell with the following content:

     A
1    text1;text2;text3;text4;text5

I'd like to divide it into five cells, one for each occurrence of ;, like this:

     B      C      D      E      F
1    text1  text2  text3  text4  text5

I know Excel has a "Text to columns" function, but I need this to be automated, so that's not an option. I've entered this in B1:

=left(A1;FIND(";";A1;1)-1)

This gets me text1, which is what I want. For the next cell I need text2. I've tried this:

=right(A1;len(A1)-len(B1)-1)

My sheet now looks like this:

     B      C
1    text1  text2;text3;text4;text5

My issue now is, that I need to remove everything after the first ;, but I can't seem to figure it out. Any ideas?

Answer 1

If you want a formula equivalent to Text to Columns and A1 contains the text to be parsed, then in B1 enter:

=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:B)*999-998,999)) and copy across.

Answer 2

Recording a macro while doing Text to Columns would have done the trick. ;)

My record:

Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
End Sub

After cleanup:

Sub SplitAndScatter()
    With Range("A:A")
        .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Semicolon:=True
    End With
End Sub

Set-up:

Result:

Hope this helps! :)