How To Split Text Into Columns In MS Excel

Columns are powerful features in Excel. With the right formatting, columns can be created to make your data readable to the readers in a way that looks great.

Sometimes you may need to split a single column of text into multiple columns.

For example, a single column may contain a table of contents or a list of data. You can split it into individual cells i.e. multiple columns using the split function.

Split Text into Columns in MS Excel

Select the text that you want

On the Data tab, in the Data Tools group, click the text to columns to open a convert text to columns wizard dialogue box

Now automatically detects your data type and selects the files type option

Automatically detects one of the following two file types

Delimited: Commas or tabs types of characters break each field and fill them into each cell

Fixed Width: Fields are aligned with columns with fixed width

Choose original data type — Choose the original data type

If the Fixed Width file type detects, click “Next” on the dialogue box and do the following

You can set or remove field widths on the next screen of the dialogue box as given below

To create a broken line at the desired position, click on that position

To delete a broken line, double-click on it

To move a broken line, click and hold down the left mouse button and drag left or right

Afterwards, click “Next” on the dialogue box, then, select one of the column data formats that best suits

Lastly, click “Finish“

If the Delimited detects, click “Next” on the dialogue box and do the following

Select one of the delimiters such as a tab, semicolon, comma, or space that your data contains

Take a look at your data preview once on the dialogue box if it is correct click “Next” or set the correct delimiter

On the next screen of the dialogue box, select column data format

Take a look at your data preview once on the dialogue box and click “Finish“. The following data has been changed from text to columns.

Split Text to Columns in Excel — Split Text into Columns in Excel

FAQ:

How to Split Text into Multiple Columns in Microsoft Excel?

Breaking down a sentence, a line, or a group of words based on the delimiters and filling them into each cell of a column is called Split Text to Columns.

What are the available options for splitting text in Excel?

You can split text using delimiters (like commas or spaces), fixed width, or by specifying a custom delimiter character.

Is it possible to preview the results before splitting the text?

Yes, Excel provides a preview window in the Text to Columns wizard, so you can see how your text will be divided before applying the split.

How can I combine columns after splitting text, if necessary?

To combine columns, you can use Excel functions like CONCATENATE or the ampersand (&) operator to merge the split columns back together.

How do I undo a text-to-columns operation if I make a mistake?

You can use the “Undo” feature (Ctrl + Z) to reverse the text-to-columns operation and restore the original data.

Category: Microsoft Excel

How to Split Text into Columns in MS Excel – 2022

Table of Contents