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

Excel’s Text to Columns function is quite helpful. Data that is contained in a single column may often be divided into many columns using this capability. However, occasionally you could experience a problem with Text to Columns in Excel while utilizing this option. The major goal of this essay is to explain why excel text to columns not working and suggest appropriate solutions.


Download Practice Workbook

You can download the practice workbook from the following download button.


4 Reasons of Excel Text to Columns Feature Not Working and Their Solutions

There may be several cases of this issue. In this part, we will discuss 4 of them with the reason behind them and their solutions.

Reason 1: Text to Columns Feature Cannot Function for Multiple Sheets Simultaneously

If any Excel users try to use Text to Columns by selecting multiple sheets, they will find out that Text to Columns has greyed out like the image below.

Text to Columns Feature Is Grayed Out

Read More: How to Convert Text to Columns with Multiple Delimiters in Excel


Solution:

Due to the limitation of Excel, we have to use Text to Columns for each sheet separately.


Reason 2: Text to Columns Function Cannot Handle Line Breaks

Suppose Excel data has line breaks like the following image. Now, let’s try to split the data into multiple cells.

  • Select column >> Data tab >> Text to Columns.

use Text to Columns in Excel

  • Convert Text to Columns Wizard -Step 1 of 3 pop-up will appear like the following picture.
  • We will select Delimited >> Next.

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

  • In this step, check comma>> select Next.

check comma

  • For this step, check General >> select Destination =$C$4>> select Finish.

check General and select Destination

  • Notice text after the line break is missing when applying Text to Columns.

text after line break is missing


Solution:

  • Now like the following image we will enter the following formula upon the selected column.
=SUBSTITUTE(B4,CHAR(10),",")

enter the following formula

  • We only require data after removing the line break. So, we will copy the data and paste it into the same destination by paste as values.

copy the data and paste it in the same destination

have cells without SUBSTITUTE function

  • We delete column B, and the previous column C will take place in 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 then appear as a consequence.
  • 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.
  • Next, choose Delimiters. The comma is used in my data, thus I decided to use them here.
  • After that, 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 I go with General.
  • To complete, click Finish.

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

  • Now the text data is separated into three different columns like 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)

I’ll start by demonstrating how to segregate the data using the Text to Columns tool.

  • First, choose the cells from which you wish to divide the data. I chose the C5:C9 cell range here.
  • Second, use Ribbon to navigate to the Data tab.
  • Choose Text to Columns thirdly.

choose the cells from which you wish to divide the data

  • As a result, Step 1 of the Convert Text to Columns Wizard will show up.
  • Choose Delimited.
  • Next, click Next.

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

  • After that, Step 2 of the Convert Text to Columns Wizard will show up.
  • Select Delimiters next. Because both commas and spaces are utilized in my data, I chose them here.
  • After that, select the option to Treat successive delimiters as one.
  • Select Next after that.

Select Delimiters next

  • The third step of the Convert Text to Columns Wizard will now show up.
  • Choose the desired column data format. I choose General here.
  • Click Finish to finish.

Choose the desired column data format

  • You notice that I missed data after that first one in the image that follows. Columns that are concealed may be the cause of this.
  • This indicates that although I have the data, the columns are concealed here. You are unable to see the data consequently.

Columns that are concealed may be the cause of this

Read More: How to Convert Text to Columns Without Overwriting in Excel


Solution:

  • Unhiding the hidden columns is the fix for Excel Text to Columns losing data because of hidden columns. Let us look at your options.
  • Start by putting your mouse cursor on the hidden column.

Start by putting your mouse cursor on the hidden column

  • Right-click there after that.
  • Next, choose Unhide.
  • Once that’s done, repeat the process to reveal the additional columns until you have your 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

Now, if the Text to Column feature is malfunctioning or unresponsive, we will take the appropriate actions as shown below.

Solution-1: Install Office Update

Now we will update Office by the image shown below:

  • Follow the steps to update the Office account: File >> Account >> Update Option >> Update Now.

Follow the steps to update the Office account


Solution-2: Open Excel in Safe Mode

To open Excel in safe mode, we will follow the below steps:

  • We press Windows+ R and the Run prompt will appear. Here we will input the following and click OK:
excel /safe

Enter excel /safe after pressing windows+R


Solution-3: Repair Office Program

Sometimes we need to open and repair Excel files to solve the problem. We follow the steps in below procedures:

  • We will open blank excel and then go to File >> Open >> This Pc.

open blank excel

  • Select Excel file >> Drop-down menu beside Open >> Open and Repair.

Open and Repair


Solution-4: Uninstall and Reinstall Office program.

If none of the aforementioned methods are successful, we will attempt the office application again by uninstalling it and reinstalling it.


Conclusion

Here, we have discussed 4 reasons for the Excel Text to Columns feature not working with their solutions. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog ExcelDemy.


Related Articles

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, i am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo