Excel Text to Columns Not Working? The Solution is Right Here!

In Microsoft Excel, the “Text to Columns” is a significant feature that allows users to split the data in a single column into multiple columns based on a delimiter. Despite having data manipulation and other excellent advantages of this feature still, users are experiencing Excel text to columns not working issues. Thus, if you are facing the same problem while handling the spreadsheet, check out this post. This blog explores common reasons and provides quick resolutions to resolve this hiccup effectively.

Quick Fixes:

How to Use Excel Text To Columns Function?

Text to Columns function in Excel can be used in two different ways: fixed-width and delimited. Out of these two methods, delimited one is much more effective but on the other hand, fixed-width is quite easy to use.

Let’s know the basic differences between these two modes.

Delimited Text Vs. Fixed-Width

At the time of splitting text to columns, it’s very important to know how and where to do that split.

Using the fixed-width splitting in Excel you can split your text after a specific number of characters. This will split the text at 27th, 14^th, or 35th characters

Whereas in the Delimited splitting mode, the splitting process starts when Excel encounters certain characters. It may do text splitting after finding any comma or space.

Why Is Text to Columns Not Working in Excel?

Sometimes, the text-to-column feature fails to work due to the following reasons:

1. Delimiter Issues- If there are unexpected characters present within the dataset while using the delimiter mode to separate data, Excel text-to-column won’t work accurately.
2. Incompatible Data Formats- Incompatible or mismatched data formats can lead to errors during the splitting process.
3. Corrupted Data- A corrupted Excel workbook or data stored within it can also cause issues with the “Text to Columns” feature.

How to Fix Excel Text to Columns Not Working Issue?

Try the below workable solutions to get rid of the issue.

Method 1- Check the Data Format

Before you use the “Text to Columns” function to split the data into multiple columns, it’s essential to check for leading or trailing spaces. Such leading space might cause unexpected issues with this Excel feature. Also, ensure there are consistent data types.

Method 2- Use Data As A Split Point

In the shown figure there is a number list with some random spaces between numbers. Now we have to split Excel data into two different columns by making use of the space like a split point.

1: Select The Data

• In this step first of all you need to select the data and then go to the data tab from the Excel ribbon. Now choose the text to columns.

2: Choose Wisely Between The Fixed With Data & Delimited Option

• As you can see in the figure, data is already separated with space so characters like tabs or commas separate each of the field. Thus we are using the “delimited” option to convert text to columns in Excel.

3: Choose The Data Split Point

• From the opened Convert Text to Columns box, choose Delimited > Next
• Well, your data is not split into the columns yet. To do this you have to check the “space” option.

• After doing this you will see that data gets split whenever the space comes in the path.
• In the appearing red-box, you can see the preview of your data and how it will look after splitting. A small vertical black line points to the location from where your worksheet data will get separated into different columns.
• So you can check easily whether your data is been split correctly or not.

4: Assign The Destination:

• After checking out each point in the preview sections press the Next.
• Now choose the destination location i.e. where to keep the split data in your worksheet.
• Tap to the finish.

In this, you can easily fix the text to columns in Excel not working issue by making use of the space just as the split point.

Also Read: Fix Arrow Keys Not Working in Excel [2023]

Method 3- Using The Fixed Width Method

1: Select The Data

• Choose the data first which you want to split.
• After that go to Data tab and hit the Text to Columns.

• From the opened Text to Columns Wizard, you have to choose the Fixed width option.

2: Choose The Data Split Point

• Hit the next button. After that, you will see that you will get to see the preview of split Excel data.
• If Excel is not guessing correctly then you can make these small changes.
• Tap to the preview option for adding the line from where each line will get split.

• Make double-click on the line if in case you want to remove it.
• Tap and drag the split line to shift to some other location.
• Hit the Next button. Now it’s time to choose how you can import each of the new columns.
• If you want to import any worksheet column with the same data format that it has, tap on that column and choose the General option from the column data type.

3: Set The Destination For New Data

• Assign some different locations to keep split data. Don’t keep it over the place where the original data already exists. otherwise, your original data will get overwritten.
• As I am here choosing the B2.
• Hit the Finish button to split up your cell text into columns.

Excel splits data exactly from where you ask it to break and then it will make two new columns. You will only feel how easy it is now to work with Excel data.

Method 4- Change Delimiter Settings to Fix Excel Text to Columns Not Working

Many times, changing or modifying the delimiter settings can resolve the delimiter issues. You can specify custom delimiters or select from predefined options like commas, tabs, or semicolons to overcome this situation.

Also Read: Excel Not Recognizing Numbers in Cells!

Method 5- Use Open and Repair Tool

As already mentioned above, corruption in the workbook is the major reason that can lead to various issues and errors including text to column not working in Excel. You can use a free Microsoft built-in utility “Open and Repair” to repair the corrupted file and tackle the situation.

Follow the below steps to learn how to use this tool:

• Open Excel then go to File >> Open.

• Navigate to the folder that contains the damaged workbook.
• Under the Open dialog box, choose the corrupted workbook.
• After that, click an arrow next to the Open button >> click the Open and Repair.

• Finally, tap on Repair button to recover as much of the worksheet data as possible.

Go With The Recommended Excel Repair Tool

If meanwhile performing the task of converting text to columns method in Excel; unfortunately you lose your Excel data then you can use the Excel Repair & Recovery Tool. This professional tool is best for easy repair and recovery of corrupted, damaged, or inaccessible Excel files. This is a unique tool to repair multiple Excel files in one repair cycle and recover the entire data in the preferred location.

Try Now       Buy Now

* Free version of the product only previews recoverable data.

It is well capable of fixing Excel file corruption issues and various errors frequently encountered by users. It allows us to easily restore entire data including the charts, worksheet properties cell comments, and other data. You can try this tool’s free demo version.

Why To Choose Excel File Recovery Software?

Steps to Utilize Excel File Recovery Tool:

Also Read: Fix Excel Cell Contents Not Visible Issue

Unique FAQs:

Why Is Split Text to Columns Greyed Out?

The split text to columns greyed out likely because you are working on a table within the spreadsheet.

What Is the Limit of Text to Columns in Excel?

255 characters is the limit of text to columns in Excel.

How Do I Enable Text to Columns in Excel?

Navigate to the ‘Data’ tab >> click on ‘Text to Columns’. Now, leave the Delimited radio option (the default mode) checked. Then, click Next. This will enable the text to columns in Excel.

What Should I Do If None of The Troubleshooting Steps Work?

Even after trying the above fixes, if the issue persists, consider seeking professional help or support from the Microsoft team.

Final Thoughts

While encountering text to columns other delimiter not working in Excel can be a frustrating issue, extracting the root causes and applying the correct resolution can help resolve this issue effectively.

However, by following the methods outlined in this blog, you can fix this problem & ensure smooth data processing in Excel.