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

Read More: How to Convert Text to Columns in Excel


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.


  • 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



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

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

Read More: How to Use Line Break as Delimiter in Excel Text to 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.


  • 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

Read More: [Fixed!] Excel Text to Columns Is Deleting Data


Simply unhide the hidden columns.


  • Place your cursor on the hidden column like in the image below.

Start by putting your mouse cursor on the hidden column

  • Right-click there.
  • Choose Unhide.
  • Repeat the process to reveal more columns as required until you can see all the expected data.

repeat the process to reveal the additional columns until you have your data

Reason 4 – Text to Column Feature Is Crashed or Not Responding

If the Text to Column feature is malfunctioning or unresponsive, there are several actions we can take to try and resolve the issue.

Solution 1 – Install Office Update

Updating Office may do the trick.

Go to File >> Account >> Update Option >> Update Now and follow the instructions.

Follow the steps to update the Office account

Solution 2 – Open Excel in Safe Mode


  • Press Windows+ R and the Run prompt will appear.
  • Input the following and click OK:
excel /safe

Enter excel /safe after pressing windows+R

Solution 3 – Repair Office Program

Sometimes repairing Excel files can solve such problems.


  • Open a blank Excel workbook.
  • Go to File >> Open >> This Pc.

open blank excel

  • Select the malfunctioning Excel file.
  • In the drop-down menu beside Open, select Open and Repair.

Open and Repair

Solution 4 – Uninstall and Reinstall the Office Application

If none of the aforementioned methods are successful, try uninstalling Office and then reinstalling it.

Download Practice Workbook

Related Articles

<< Go Back to Splitting TextSplit in ExcelLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF