Splitting text form a series in column A, into seperate coloumns

[Mystz0r]

Hey guys,

I really hope you can help me with this:

When updating my stock investment excel sheet with raw data from my broker, I get one single string of text into one cell (A1). My challenge is to seperate each of these values into seperate columns.

Example: This is the cell I get when extracting data (A1):
2011-05-27;21,6;22,3;21,4;21,7;1046801

I want my sheet to seperate each value, into seperate columns, ai.:

B1: 2011-05-27
C1: 21,6
D1: 22,3
E1: 21,4
F1: 21,7
G1: 1046801

I get around 150 entries of data when loading (thus filling up the first 150 rows of my sheet), so whatever code has to be "dragable" downwards.

Would really appreciate any help - thanks a lot in advance

Soren

 

[Sektor]

This code looks for all values in column A and transfers found data into adjacent columns.

Sub SplitBrokerData()

    Dim arr As Variant, i As Long
    
    For i = 1 To Range("A1").End(xlDown).Row
        arr = Split(Cells(i, 1), ";")
        Cells(i, 2).Resize(, UBound(arr) + 1) = arr
    Next
    
End Sub

 

[Sandeep Warrier]

Hi & welcome to the Board

Select all data in column A (A1:A100 for example)

Press Alt+D+E. Select Delimited and click Next.

Put a check against Semicolon and click Next.

In Destination, put (or select) B1 and click Finish.