excel deleting zeros - this is a huge problem :)

Hi guys,

I am importing data to excel from txt file. First I need to use "text to columns" option to get the data i need in separate columns, which works very good, however this option would delete all zeros infront of the number to number without zeros. e.g. 003845 to 3845.

The problem I am facing here is I can not use custom format cell option, because i do not know how many zeors is infornt of each number (it is not point to compare txt file with excel file), besides the data i import and use it after "text to columns" option consists text as well, not only numbers.

for an example:

abcd
3845
asdasdasdasd
29911

I would like to keep it that way, because all further formulas i use, are based on istext and isnumber function, it is just that i would like to have zeros back. :D

Is it possible to solve my problem?

If you select TEXT in the last option in "Text to Columns" you will retain the zeros.


0018 data
001bds data
000433 data
sadasdas data
000056 data

Thank you everyone for you nice replies. You are all correct, your all solutions work, however they would change all cells to text. But for me it is esssential that cells which contain only numbers after (trim, text to collumnm option) would remain number cells, however would have the zeros included.

It is only by using text that you can retain the correct number of leading zeros: why are the leading zeros important in any formula you use?

Well the problem is this data is send further, where there is a custom formated cell already and matches exact number... 00034 must be 00034... if there would be 34, it would be a problem

besides, if i know which cell is number only and which is text i can apply different formula for each cell, which is a well important for the data.

Well the problem is this data is send further, where there is a custom formated cell already and matches exact number... 00034 must be 00034... if there would be 34, it would be a problem

besides, if i know which cell is number only and which is text i can apply different formula for each cell, which is a well important for the data.

Well the problem is this data is send further, where there is a custom formated cell already and matches exact number... 00034 must be 00034... if there would be 34, it would be a problem

besides, if i know which cell is number only and which is text i can apply different formula for each cell, which is a well important for the data.

Well the problem is this data is send further, where there is a custom formated cell already and matches exact number... 00034 must be 00034... if there would be 34, it would be a problem

besides, if i know which cell is number only and which is text i can apply different formula for each cell, which is a well important for the data.