[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.


Download Practice Workbook

You can download the practice workbook from here.


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

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.

Dataset for Excel Text to Columns Is Deleting Data


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.

Having Hidden Columns in Excel

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

Text to Columns Delete data for Having Hidden Columns in Excel

  • 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.

Convert Text to Columns Dialog box for Deleting Data in Excel.

  • 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.

Unhide Hidden Columns to Get Lost Data

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

Unhiding Columns to Solve Excel Text to Columns Deleting Data

  • 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 Use Text to Columns Feature with Carriage Return 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.

Considering Line Break as Space

Here, I used the Text to Columns feature by following the same steps from Reson-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.

Considering Line Break as Space as A Result Text to Columns is Deleting Data in Excel

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: How to Use Line Break as Delimiter in Excel Text to Columns


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.

Set Line Break as Delimiter

  • 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.

Set Line Break as Delimiter to Sove Excel Text to Columns Deleting Data

  • 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 Text to Columns with Multiple Delimiters in Excel


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. For, more articles stay connected with ExcelDemy. And, if you have any questions then let me know in the comment section below.


Related Articles

Mashhura Jahan

Mashhura Jahan

Hey! Welcome to my profile. Currently, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo