You pasted data to your Excel worksheet but everything is separated with semicolons and unreadable? No worries, Ted Jordan has the solution!
Split semicolon separated values into columns in Excel with the Text to Columns Wizard tool. If you want to convert semicolon lists to rows, the TRANSPOSE trick will become one of your favourites!
We’ll explain how to convert semicolon (or comma) separated values to columns and we’ll share with you more Excel tips and tricks such as Text to Columns or TRANSPOSE.
Ready to become an Excel Master?
Split semicolon separated values in Excel
Split semicolon lists or comma separated text into Excel columns with Text to Columns Wizard. If you want to switch from columns to rows, use the TRANSPOSE pasting trick.
Text to Columns: convert semicolon lists to columns
Learn how to use Text to Columns with semicolon lists, continue to read.
In this example, we want to split semicolon separated values into columns.
We select the data to split, go to Data then click Text to Columns. We choose “semicolon” as the delimiter… and voilà!
Now, we just need to format our Excel spreadsheet to make it look more professional.
Learn how to use Text to Columns in Excel here.
From semicolon separated values to rows
To convert semicolon separated text to rows, follow the steps mentioned above then use the TRANSPOSE trick.
As a reminder, the TRANSPOSE formula is =TRANSPOSE(array) but here we want to transpose data and paste formatting too so we use the TRANSPOSE trick.
Learn how to rotate columns to rows in Excel with the TRANSPOSE function or the TRANSPOSE trick with this free Excel tutorial!
In this example, we formatted the table from the previous example and rotated columns to rows with our favourite TRANSPOSE trick.
How to convert columns to semicolon separated list?
If you wish to convert Excel columns to semicolon separated values, as a list, use CONCAT or CONCATENATE functions.
In this example, we want to convert columns A to C to semicolon separated text and we decide to use CONCAT. Here is how to do it:
– Add a semicolon in cell D21 to make the process easier.
– Type =CONCAT(A17,$D$21,B17,$D$21,C17) to transform the first row.
If you wonder why there is a dollar sign $ in the formula, check this article. You will discover how to lock range or cell reference and how to do it using a handy shortcut.
– Drag the A23 cell down so the CONCAT function is applied to all rows.
Now, you know how to split semicolon separated values in Excel into columns or into rows; you can apply the same techniques with comma separated text. And you are also able to convert a column to a semicolon list easily.
You also discovered Text to Columns, a TRANSPOSE trick and the CONCAT function.
Continue to expand your Excel knowledge: join our Online Course and get MS Excel certified!
