Forum Discussion
How to Stop Excel from Translating E as Zero Digits
Hey, I have a list of codes that use a combination of numbers and letters. The problem occurs when a code contains the letter "e", in which Excel keeps changing the value after the letter "e" to zeros. For example, with the code 12345E5, Excel translates it as 1234500000. I have tried to change the format to text, number, and custom (I use 0000000 because the code length is seven digits) and still can't get the expected result.
If I only got a small number of codes, I can simply add an apostrophe before the code, but it is messy and unpractical because there are 1000+ codes to insert.
Here's a sample of my problem.
Please help if anyone has a solution or a workaround. Thanks in advance
2 Replies
Adibman .... If you manually enter the data ("1000+"?!), you can format the cell as Text __before__ you enter the data. Caveat: Changing the cell format after you enter the data incorrectly does not correct the data.
If you are "entering" the data from a CSV file, do not open the CSV file directly.
Instead, in Excel, __import__ the CSV file. In my version of Excel, I click Data > Get External Data > From Text. You might do it differently in your version.
Anyway, in the last dialog box of the Text Import Wizard, select the column(s) with the data and select Text under Column Data Format, then click Finish > OK.
- I tried to import it as a CSV file and it worked. Thanks a lot
