1

I have a lot of cells which I want them to be formatted as texts. However according to Excel they are not texts and hence should be corrected to numbers instead. For example, "6814070833E000101" is a text to me but they gave an error (marked by green tip at the top left hand corner). I tried to click "ignore my error" and converted the file to CSV, but the cells with errors got corrected upon conversion. The mentioned text became "6.81E+110",

I need to have the file in CSV because I would need to use the file for further analysis using a statistical software.

How can I rectify this?

0

Save a backup as .xlsx Then ctrl-a will select all the cells on the sheet. You can then right-click and select format cells. Choose number then select Text.

This conversion should have all the cells exactly as you desire with the possible exception of dates. If everything looks correct, save as csv and continue.

If you have date fields, either select the cells with dates and change the format to Dates. Or redo entire conversion using the backup, and select only the number cells.

2
  • This is exactly what I had done. I had converted everything to text and the new formatting gives me the display that I want. However, the green tip at the corner of the cell is still visible, suggesting an error. As long as there is an error, CSV will correct the cell into the correct format, which is in the number format in this case.
    – HNSKD
    Nov 2 '16 at 1:33
  • Hmm, when I go into the csv file with notepad, I do see the leading zero's for 00003 (set to text). Can you confirm your csv data with notepad or some other editor? Also what happens if you save as .txt? (do you get a 3 or 00003)?
    – bvaughn
    Nov 2 '16 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.