How do I convert a delimited list to a multicolumn table in excel?

Question

I have a list like this:

Friut;Taste;Color;Other;Apple;Good;Red and Blue;1;Orange;Really Good;Orange;12

And I want to convert it by selecting every 4 delimitations and turning them into rows like this:

Fruit    Taste     Color     Other
Apple    Good      Red and G.1
Orange   Really Go.Orange    12

How is this possible with Libreoffice (preferred), Openoffice, or Excel?

Edit: The above is an example. I am using it to select 4 delimitations, but there are about 500 rows that I need this to work on.

Edit2: Provided my own (correct) answer

Answer 1

My answer came from user49740:

sed -re 's/([^;]*);([^;]*);([^;]*);([^;]*);?/\1,\2,\3,\4\n/g' input.txt > output.txt

The first half of the string:

([^;]*);([^;]*);([^;]*);([^;]*);?

Finds 4 groups of ([^;]*) (all characters before the semicolon) then a semicolon. The second half:

\1,\2,\3,\4\n

Changes the text to CSV format. > output.txt will write the converted file to output.txt.

Answer 2

VBA macro to split entire rows to multiple rows

With this macro you split entire rows to multiple rows. You can choose how many columns your want after splitting. just change the value for iSplit at the first line. I don't use a specific delimiter, just column counts.

I commented every step. It's easy to adjust the macro to your personal needs.

1. Open Excel and VBA editor with Alt+F11
2. On the left pane, paste the code under the sheet where your data is placed
3. Modify the first two lines according to your needs
4. Execute the macro with F5

---

Const iSplit = 4    '## how many columns do you want after splitting

Sub transposeColumn()
    '## search the last row to know how many rows we have to iterate through
    iLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    '## begin to loop through every row. Begin at last row and go upwards
    For r = iLastRow To 1 Step -1

        '## search the last column in the current row
        iLastCol = Rows(r).Find("*", Cells(r, 1), , , xlByColumns, xlPrevious).Column

        '## calculate how many new rows we need to insert for this row
        iNewRows = WorksheetFunction.RoundUp(iLastCol / iSplit, 0) - 1

        '## begin to copy and insert new rows, one by one
        For c = 1 To iNewRows

            '## insert a new blank line where we can copy values to
            Rows(r + c).Insert Shift:=xlDown

            '## set the source range for easier access later
            Set rngSrc = Range(Cells(r, iSplit * c + 1), Cells(r, iSplit * c + iSplit))

            '## copy and paste the range
            rngSrc.Copy Destination:=Cells(r + c, 1)

            '## clear all cells which we have just copied
            rngSrc.Clear
        Next c
    Next r
End Sub

Answer 3

If the list is a text file, you can use the Text Import Wizard in Excel. I assume there is a similar tool in both LibreOffice and Openoffice.org.

However, since the delimiters are the ; signs, when you import there will be a need to arrange the data as it will be imported as a row of values. You can alter the original text file and add another delimiter that signifies the next row.