Splitting text strings without using formulas

Excel 2016
When working with data in Excel spreadsheets, it is sometimes necessary to split text lines into columns. Excel offers many different functions for working with text strings, but you don't have to use formulas for this task:
Convert text to columns in Excel 365

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 the 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 option to split data separated by delimiters such as commas, spaces, slashes, or other characters,
  • Select the Fixed width option to split data spanning the same number of characters,

Then click Next >:

Convert text to columns in Excel 365

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

  • f you are working with delimited data, in the Delimiters group, choose the correct delimiter for the data (by default, the Tab delimiter is used, but often the text is delimited by commas or space):
    Convert text to columns by delimeters 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.

  • If you are working with fixed-width data, specify the column breaks directly in the Data preview area:
    Convert text to columns with fixed-width data in Excel 365
  • 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 the 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 button.

Excel converts the text you selected:

Converted text to columns in Excel 365

See also this tip in French: Fractionner des chaînes de texte sans utiliser de formules.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.