The situation
We have an extensive list of numbers in different digits, say from 10 to 13 digits. The problem is, they are supposed to be numbers in thirteen digits stored as text. (Of course, in our example, we work with a short list of twenty numbers only 😉)
Our task is to convert them back to 13 digits as text, not numbers as shown below:
The solution is indeed quite simple. It can be solved with the following formula:
=TEXT(A2,"0000000000000")'copy down
where A2 resides the value we want to convert;
the 13 digits of "0" is the format string that we want
As simple as this. If 15 digits is required, simply revise the second argument to fifteen zeros (enclosed with a pair of quotation marks) accordingly. Nonetheless, have you wondered why it happened in the first place? This is indeed the main point of this blogpost. 😁
Most of the time, the data is coming from a system. And in many scenarios, the data files are exported as CSV/TXT file, which is good. Let’s look at the following screenshot.
From the “Preview” of the data file from File Explorer, we can see that the Product Code are texts in 13 digits, with leading zeros. When the file is opened with Notepad / WordPad (or other text applications), we see the same:
However, when the same file is opened with Excel, Excel eats all the leading zeros and convert the texts into numbers.
This is the moment when Excel is too smart. 🤦♂️
What’s happening?
When CSV file is opened with Excel, Excel turns texts as numbers into numbers automatically. As a result, all leading zeros are gone. When we save the file, even as csv file, the leading zeros are gone. That’s a common source of the problem data that needs to be fixed, and most people are not even aware of it.
Well, it is ironic. Excel creates a problem that did not exist at the first place; and then provides ways to fix it. (Sound like how the business world operates... )
If we want to preserve the format of the data source, we could open the file with Notepad / WordPad / or other text applications… What????? Who will do that? Excel is the best tool to do it, isn’t it? And all we know, Excel is the default program to open CSV file when we double-click the file.
So now the question is…
How to open CSV/TXT file with Excel preserving the source format?
The best way is to get data with Power Query, which will be the topic of next post. Stay tuned!
However, we would like to edit the data sometimes. In this scenario, we need to open the file, revise the data, and then save it. The key is, we cannot open the csv/txt file directly, we need to seek help from wizard, the legendary Text Import Wizard 🧙♂️.
If you are using Excel 365, you may have no clue where the Text Import Wizard is because it is very hidden.
To display it, go to File -> Excel Option -> Data -> Check the “From Text (Legacy)” -> OK
Then you should see it on the Data tab.
To activate it, go to
- Data
- Legacy Wizards
- From Text (Legacy)
Then select the csv/txt file that you want to import to Excel:
Now answer the 🧙♂️!
Step 1
In most cases, we select “Delimited”. Comma and Tab is the common delimiter for csv and txt file, respectively.
Also check the box “My data has headers” (depends on your data file of course).
Normally, we don’t have to worry about the “File origin” as the Wizard is good at detecting that.
Step 2
Check the applicable Delimiters (i.e. the marker(s) to split data into columns)
When you have picked the correct delimiters, you should see your data split into columns nicely in the Data preview pane.
Step 3 – The crucial step
This is the crucial step to preserve the data format. In our example, we select the first column and mark it as “Text“; select the second column and mark it as “General“.
(Tip: We may skip columns that we don’t need)
Final Step – Choose the destination
Here we go!
The csv/txt file is properly opened with Excel. I know, it takes many more steps, but this is the proper way to open/edit a csv/txt without unexpected change in data.
Laziness (or convenience) has a price to pay, sooner or later. 😁
wmfexcel 