[Fixed!] Excel Text to Columns Is Deleting Data

Text to Columns is a very useful feature in Excel. This feature is generally used to separate data into multiple columns that are stored in a single column. But, sometimes while using this feature you may face a situation where Text to Columns is deleting data in Excel. The main objective of this article is to explain the reasons behind this problem with suitable solutions.

Excel Text to Columns Is Deleting Data: 2 Reasons with Solutions

To explain this article, I have taken the following dataset. It contains Serial No. and Information about 5 students. There are First Name, Last Name, Student ID, and Birth Year. All of this information is stored in a single column. I will use Text to Columns to store this information in different columns. And explain why Text to Column is deleting data in Excel with solutions.

Reason 1: Having Hidden Columns in Excel

To begin with, I will show you how you can use the Text to Columns feature to separate the data.

Firstly, select the cells where you want to separate the data. Here, I selected the cell range C5:C9.
Secondly, go to the Data tab from the Ribbon.
Thirdly, select Text to Columns.

Consequently, the Convert Text to Columns Wizard – Step 1 of 3 will appear.
Select Delimited.
Then, select Next.

Afterward, the Convert Text to Columns Wizard – Step 2 of 3 will appear.
Next, select Delimiters. Here, I selected Comma and Space because both of these are used in my data.
Then, check the Treat consecutive delimiters as one option.
Further, select Next.

Now, the Convert Text to Columns Wizard – Step 3 of 3 will appear.
Select the Column data format you want. Here, I selected General.
Finally, press Finish.

Here, in the following image, you can see that I lost data after that first one. The possible reason behind this can be hidden columns.
This means I have got the data but the columns are hidden here. As a result, you are not being able to see the data.

Solution: Unhide Hidden Columns to Get Lost Data

The solution for Excel Text to Columns deleting data due to hidden columns is unhiding the hidden columns. Let’s see how you can do it.

In the beginning, place your mouse cursor on the hidden column.

Next, Right-click there.
Then, select Unhide.

Now, you will see that one of the hidden columns is showing and you have got your data.

After that, unhide the other columns in the same way until you get your data.

Finally, give the columns titles as you want. Here, in the following picture, you can see that I have given titles to the columns and I have got my desired output.

Reason 2: Considering Line Break as Space

In the following picture, you can see another dataset. Here, the data are separated by space. I will represent these data in 4 different columns in Excel by using the Text to Columns feature.

Here, I used the Text to Columns feature by following the same steps from Reason-1. But, in the following image, you can see the final output. Here, you can see that Text to Columns is deleting data after the first 2 columns in Excel. The reason behind this can be considering line break as space.

Let’s see how you can check that.

To check that, click on the cell where the data is stored.
Now, in the formula bar, you will be able to see that after the first two data, there is a line break. But, you did not select line break in the Delimiters.

Read More: Excel Text to Columns Not Working

Solution: Set Line Break as Delimiter

Here, I will show you how you can set line breaks as a delimiter so that Text to Columns does not delete data in Excel. Let’s see the steps.

Firstly, select the cells where you want to use Text to Columns.
Secondly, go to the Data tab.
Next, select Text to Columns.

After that, the Convert Text to Columns Wizard – Step 1 of 3 will appear.
Select Delimited.
Then, select Next.

Next, the Convert Text to Columns Wizard – Step 2 of 3 will appear.
Afterward, select Space as Delimiters.
Then, check Others and press Ctrl + J to get the line break character.
Further, check the Treat consecutive delimiters as one option.
Subsequently, select Next.

Consequently, the Convert Text to Columns Wizard – Step 3 of 3 will appear.
Select the Column data format you want. Here, I selected General.
Lastly, press Finish.

After that, you will see that you have got all the data in different columns.

Finally, give the Column Titles as you want and you will get your desired dataset.

Download Practice Workbook

You can download the practice workbook from here.

Text to Column Deletes Data.xlsx

Conclusion

In this article, I tried to explain why Excel Text to Columns is deleting data with solutions. Here, I explained 2 different reasons and their solutions. I hope this article was helpful to you. And, if you have any questions then let me know in the comment section below.

<< Go Back to Excel Text to Columns | Splitting Text | Split in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!