Splitting text strings without using formulas

Excel 2016 2013 2010 2007 2003
If you want to paste text in your worksheet, you can use Excel's Convert Text to Columns Wizard.

To do this, follow the next steps:

   1.    Select cells with the text that should be split.

   2.    On the Data tab, in the Data Tools group, click Text to Columns button:

Data Validation button in Excel 365   or   Data Validation button in Excel 365

   3.    In the Convert Text to Columns Wizard - Step 1 of 3 dialog box, select the Delimited options, then click Next >:

Convert text to columns in Excel 365

   4.    In the Convert Text to Columns Wizard - Step 2 of 3 dialog box:

  • In the Delimiters group, choose the correct delimiter for the data (by default the Tab delimiter is used, but often the text delimited by commas or space):
    Convert text to columns in Excel 365

    Note: Even if you are pretty sure about the delimiter, you need to change it if you see incorrect data in the Data preview area.

  • Click the Next > button.

   5.    In the Convert Text to Columns Wizard - Step 3 of 3 dialog box:

  • Select each column and set the Data Format if needed:
    Convert text to columns in Excel 365

    Notes:

    1. You can skip this step and format columns after converting.
    2. You can click the Advanced... button to select the correct Decimal separator and Thousands separator (see how to change used by default decimal symbol and digit grouping symbol):
      Advanced in Convert text to columns in Excel 365
    3. You can skip some columns for converting by selecting the Do not import column (skip) in the Column data format group.
  • Click the Finish.

Excel converts the text you selected:

Converted text to columns in Excel 365

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.