Dealing with the issue of Excel text to columns not working and looking for some quick fixes to get over it?
Well its quiet tough to identify the exact location where you are making mistake in using text to column function in Excel. So it’s my suggestion for you to go through the complete steps of this post to identify where you are lacking.
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 effective but on the other hand fixed-width is quite east 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, 14th, or 35th characters
Whereas in the Delimited splitting mode the splitting process starts when Excel encounters certain characters. It may does text splitting after finding any comma or space.
To recover corrupted Excel file data, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
How To Fix Excel Text To Columns Not Working Issue?
Method 1# Use Data As A Split Point
In the shown figure there is a number’s 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 that how it will look like after splitting. Small vertical black line points the location from where your worksheet data will get separated into different columns.
- So you can check easily whether your data is been splited 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 into your worksheet.
- Tap to the finish.
In this you easily fix Excel text to column not working issue by making use of the space just as the split point.
Method 2# 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 for shifting 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, then tap to that column and choose the General option from the column data type.
3: Set The Destination For New Data
- Assign some different location to keep split data. Don’t keep it over the place where the original data already exist. otherwise your original data will get overwritten.
- As I am here choosing the B2.
- Hit the Finish button for splitting up your cell text into columns.
Excel split data exactly from where you ask it to break and then it will make two new columns. You will only feel that how easy it’s now to work with Excel data.
Note: Excel will also remove the leading spaces present in the cells of second column. Very conveniently..!
Go With The Recommended Option
If meanwhile performing the task of converting text to columns method in Excel; unfortunately you lose your Excel data then you can use Excel File 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 at one repair cycle and recovers the entire data in the preferred location.
It is well capable of fixing Excel file corruption issues and various errors frequently encountered by the users. It allows us to easily restoring 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?
- Easily restore Excel worksheet data like chart, formula, chart sheet, table, image, and much more…
- Retains the structure originality of your corrupted Excel workbook in the recovered file.
- Performs quick Excel file recovery
- Recovers any type of corrupted Excel xls/xlsx files instantly
- Well supports for MS Excel 2019/ 2016/ 2013/ 2010/ 2007/ 2003/ XP/ 2000/ 97/ 95 versions.
- Not only repair XLS/XLSX files but also fixes several Excel errors
Steps to Utilize Excel File Recovery Tool:
So now you must have understood how you can fix Excel text to column not working issue by choosing the right method as per your data.
Now you all can use Text-To-Columns feature in Excel without any problem just like a pro.