How to Split Text into Columns in MS Excel – 2022

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
Delete or Create a Break Line
Delete or Create a Break Line
  • Afterwards, click “Next” on the dialogue box, then, select one of the column data formats that best suits
  • Lastly, click “Finish
Choose Column Format 1
Column Data Format

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
Select Delimiters
Select Delimiters
  • 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
Choose Column Data Format
Choose 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

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.

Share