Excel always automatically turns value I insert into gibberish numbers.

Occasional Contributor

Hi all,

 

I am working on this file inserting data into .csv file.

 

Tha data include several sets of numbers separated by commas (eg. 24126,341255,512581, and so on)

Whenever I insert them into a cell, save the file, close it and reopen, the data in the cell displays as ######## and when I look closely at the cell, all the values turn into a gibberish number like 1.25530629077714E+115.

 

I discovered that the only way to solve this is to add a space in front of the value and after each comma ( 24126, 341255, 512581)

This method is very impractical. Is there any other way to stop excel from automatically formatting values I put in the cell? If yes, this would help me save a lot of time in my workflow.

 

Thank you!

Pjuvish

 

4 Replies

@pjuvish Excel merely "thinks" you are inserting a very large number, and converts it to scientific notation (i.e. the number with E+ in it). To avoid this, format the column/range where you want to input the strings with numbers and commas as Text, before you enter these strings.

Hi Riny_van_Eekelen,

Thank you for your advice.
I tried doing this but when I close and reopen the file, excel stills automatically change the format to scientific.

Is there a way to permanently disable this function?
Hi - I doubt it can be permanently disabled but you could try to enter the number with a leading single quote (') as a comment character.
Also, make sure to not save the file as csv or all formatting and the apostrophe as well will be lost.