Excel – Split text into different columns with the Convert Text to Columns Wizard

excel

Last updated on February 5th, 2024 at 02:09 pm

Read Time:1 Minute, 52 Second

If you exporting data into CSV or text files then this guide may come in handy for you…

You can take the text in one or more cells, and spread it out across multiple cells. This is called parsing, and is the opposite of concatenating, where you can combine text from two or more cells into one cell.

For example, if you have a column of full names, you can split that column into separate first name and last name columns, like this:

Before and after of text split into different columns

Go to Data > Text to Columns, and the wizard will walk you through the process. Here’s a full breakdown of how it works:

Select the cell or column that contains the text you want to split.

NOTE:  Select as many rows as you want, but no more than one column. Make sure there’s enough empty columns to the right so nothing over there gets overwritten. If you don’t have enough empty columns, add them.

Click Data >Text to Columns.

Click the Data tab, and then click Text to Columns

This starts the Convert Text to Columns Wizard. Click Delimited > Next.

Check Space, and clear the rest of the boxes, or check Comma and Space if that is how your text is split (Smith, John, with a comma and space between the names). You can see a preview of your data in the Data preview window.

Step 2 in the Convert Text to Columns Wizard

Click Next.

In this step, you pick the format for your new columns, or you can let Excel do it for you. If you want to pick your own format, select the format you want, such as Text, click the second column of data in the Data preview window, and click the same format again. Repeat for all the columns in the preview window.

Step 3 in the Convert Text to Columns Wizard

Click the Collapse Dialog Box button image button to the right of the Destination box to collapse the dialog box.

Select the cells in your workbook where you want to paste your split data. For example, if you are dividing a full name into a first name column and a last name column, select the appropriate number of cells in two adjacent columns.

Select the cells where you wnt to paste your split cells

Click the Expand Dialog Box button image button to expand the dialog box, and then click Finish.

Expand the dialog box when you're done selecting your cells.

COMMENTS

If you have any questions or feedback on this guide, please leave us a message below.

Click to rate this post!
[Total: 0 Average: 0]

Free Subscription

If you want to be notified when we post more quality guides like this one, sign up to our free subscription service and you will receive an email when a new post is live.

Join 441 other subscribers.

No need to worry, we will not be filling your inbox with spam and you can unsubscribe anytime you like.


Leave us a message...

This site uses Akismet to reduce spam. Learn how your comment data is processed.