How to Split Text to Columns in Excel – 2022

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

Steps to Split Text to Columns in MS Excel:

  • Select text that you want to split text to columns
  • On the Data tab, in the Data Tools group, click 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 filed and fill them into each cell
    • Fixed Width: Fields are aligned with columns with fixed width
Choose original data type
Choose 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 break line at the desired position, click at that position
  • To delete a break line, double click on it
  • To move a break 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
How to Split Text to Columns in Excel - 2022 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 a 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 which has been changed as text to columns.
Split Text to Columns in Excel
Split Text to Columns in Excel

Other Amazing Posts: