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.

use Text to Columns in Excel

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

Select Delimited.
Click Next.

Convert Text to Columns Wizard -Step 1 of 3 pop up

In the next window, check Comma and select Next.

check comma

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

check General and select Destination

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

text after line break is missing

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),",")

enter the following formula

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.

copy the data and paste it in the same destination

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

have cells without SUBSTITUTE function

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

delete the column B, and the previous column C will take place in column B

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

Select Delimited.
Click the Next button.

Step 1 of the Convert Text to Columns Wizard

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.

Text to Columns Wizard will then display Step 2

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

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

Convert Text to Columns Wizard's third step will now appear

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

text data is separated in three different columns

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.

choose the cells from which you wish to divide the data

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

Choose Delimited.
Click Next.

Step 1 of the Convert Text to Columns Wizard will show up. Choose Delimited

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.

Select Delimiters next

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

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

Choose the desired column data format

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.

Columns that are concealed may be the cause of this