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.
Read More: How to Undo Text to Columns in Excel
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.
Read More: How to Convert Column to Text with Delimiter in Excel
Download Practice Workbook
You can download the practice workbook from here.
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.
Related Articles
- How to Use Text to Columns in Excel for Date
- How to Convert Text to Columns Without Overwriting in Excel
- How to Split Text to Columns Automatically with Formula in Excel
- How to Convert Text to Columns with Multiple Delimiters in Excel
- How to Convert Text to Columns in Excel with Multiple Spaces
- How to Use Text to Columns Feature with Carriage Return in Excel
