Al Ikram Amit
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.
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.
- Convert Text to Columns Wizard -Step 1 of 3 pop-up will appear like the following picture.
- We will select Delimited >> Next.
- In this step, check comma>> select Next.
- For this step, check General >> select Destination =$C$4>> select Finish.
- Notice text after the line break is missing when applying Text to Columns.
Solution:
- Now like the following image we will enter the following formula upon the selected column.
=SUBSTITUTE(B4,CHAR(10),",")
- 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.
- So, notice in the image we just have cells without the SUBSTITUTE function.
- We delete 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.
- 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.
- 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.
- Now the text data is separated into three different columns like the image below.
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.
- As a result, Step 1 of the Convert Text to Columns Wizard will show up.
- Choose Delimited.
- Next, click Next.
- 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.
- 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.
- 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.
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.
- 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.
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.
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
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.
- Select Excel file >> Drop-down menu beside Open >> 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.
