Excel Text to Columns Not Working (4 Reasons with Solutions)

In this article we will investigate some reasons why Excel’s Text to Columns feature might not be working as expected, and suggest appropriate solutions.

Reason 1 – Text to Columns Feature Can’t Be Applied to Multiple Sheets Simultaneously

If you try to use Text to Columns while multiple sheets are selected, the Text to Columns button on the ribbon will appear greyed out like in the image below.

Text to Columns Feature Is Grayed Out

Solution:

This is currently a limitation in Excel. You’ll have to apply Text to Columns in each sheet separately.

Reason 2 – Text to Columns Function Can’t Handle Line Breaks

Suppose we have some data containing line breaks like in the following image. Let’s try to split the data with Text to Columns into multiple cells.

Steps:

Select the column.
Go to the Data tab and click on Text to Columns.

The Convert Text to Columns Wizard -Step 1 of 3 pop-up will appear, like in the following picture.

Select Delimited.
Click Next.

In the next window, check Comma and select Next.

Check General.
Enter $C$4 in the Destination box.
Click Finish.

The text after the line break is missing after applying Text to Columns.

Solution:

Steps:

Like in the following image, enter the following formula and copy it to the rest of the cells in the column:

=SUBSTITUTE(B4,CHAR(10),",")

To replace the formulas with values, copy the data and paste it in the same place by right-clicking in the first cell and selecting Paste As Values from the Context Menu.

Now we just have text values, without the SUBSTITUTE function.

Delete column B and the previous column C will shift left and become column B.

Step 1 of the Convert Text to Columns Wizard will appear.

Select Delimited.
Click the Next button.

The Convert Text to Columns Wizard will then display Step 2.

Choose Delimiters.
Select the comma, since we used it in our dataset.
Click Next.

The Convert Text to Columns Wizard’s third step will now appear.

Select the column data format that you want, here General.
Click Finish.

Now the text data is separated into three different columns like in the image below.

Reason 3 – Text to Columns Is Not Showing Data Due to Hidden Column(s)

We’ll start by demonstrating how to split the data using the Text to Columns tool.

Steps:

Choose the cells which contain the data to divide, here cells C5:C9.
Go to the Data tab.
Choose Text to Columns.

Step 1 of the Convert Text to Columns Wizard will appear.

Choose Delimited.
Click Next.

Step 2 of the Convert Text to Columns Wizard will appear.

Select Delimiters. Because both commas and spaces are utilized in our data, we select them both.
Select the option to Treat successive delimiters as one.
Click Next.

The third step of the Convert Text to Columns Wizard will now appear.

Choose the desired column data format, here General.
Click Finish.

It appears that we are missing data – the Surnames and columns beyond are not displayed. Hidden columns are the cause. The data is actually there, we just can’t see it because the columns are concealed.