Split up a cell by every ;

Use this, copied across and down:

=TRIM(MID(SUBSTITUTE(";"&'Sheet1-Before'!$A5,";",REPT(" ",125)),125*COLUMNS($A:A),125))

Since you're in Sweden, you'll probably need this:

=TRIM(MID(SUBSTITUTE(";"&'Sheet1-Before'!$A5;";",REPT(" ";125));125*COLUMNS($A:A);125))

using ; as a separator, not the , that the rest of us use...

Sub Text2Columns()
    
    Rows("1:2").Delete Shift:=xlUp
    Rows("2:2").Delete Shift:=xlUp
    
    Columns("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Columns("A:A").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), Array(6, 1), _
        Array(7, 1)), TrailingMinusNumbers:=True
    
    Range("A1").CurrentRegion.AutoFilter
    
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 12.22
    Columns("C:C").ColumnWidth = 9.11
    Columns("D:D").ColumnWidth = 10.56
    Columns("E:E").ColumnWidth = 10.67
    Columns("F:F").ColumnWidth = 7.11
    
End Sub

Anywhere at all. I tested it in your AFTER sheet, cell A2

This can be easily achieved with the help of Text to Columns inbuilt feature as suggested by AB33.

Follow these steps to achieve the desired output...

On Sheet Sheet1-Before

1) Delete rows 1, 2 and 4.

2) Select whole column A.

3) On Data Tab --> Text to Columns --> Make sure Delimited radio button is selected --> Next --> Select Semicolon under Delimiters options --> Next --> Finish

That's it.

Unfortunately, in this instance, text to columns does not completely fulfil the brief because it will not get rid of trailing spaces:

As you can see the last ; character is just an end of the data. There might also be spaces in the data so I would have to trim the cells.

Originally Posted by AliGW

Unfortunately, in this instance, text to columns does not completely fulfil the brief because it will not get rid of trailing spaces:

I used the Replace function to remove spaces. See my code in post #8.

You have a point here Ali. Didn't read the original post carefully.
But the extra spaces can be removed at once from all the data with another inbuilt feature called Replace which can be invoked with Ctrl+H

Yes, sure it can, but it means a two-stage process, whereas the other two solutions offered are one-stage.

@AlphaFrog - your VBA solution was not being referred to - it was AB33 and Sktneer's proposals.

Originally Posted by AliGW

Yes, sure it can, but it means a two-stage process, whereas the other two solutions offered are one-stage.

@AlphaFrog - your VBA solution was not being referred to - it was AB33 and Sktneer's proposals.

You have a brilliant future Ali!

Originally Posted by sktneer

You have a brilliant future Ali!

LOL!!!