MrExcel Publishing
Your One Stop for Excel Tips & Solutions

scientific format problem


Posted by Lou on December 05, 2001 3:51 PM

While working as a data inputter from home, i have come across a problem i can't work out. Column A is formatted to General and is used for inputting 'part numbers containing text, numbers, slashes and dashes. Every time i input a 12 digit, numeric number, it converts itself to scientific format. When i go into formatting, the example display for General, Text and Numeric all display a scientific format. The only way round it i can find is to format to Custom, 0. Why is the data converting when its been set to a certain format?


Posted by Mark W. on December 05, 2001 4:15 PM

Interesting...must be a bug...

Since you're inputting part numbers and won't be
performing arithmetic opertions on 'em you should
format 'em as Text rather than 0. The only problem
is that once Excel applies Scientfic Notation to a
value such as 123456789012 (producing 1.23457E+11)
the application of the Text format still displays
the Scientific Notation form until you re-activate
the formula bar (why I don't know). The morale of
this tale is always format as Text before entering
123456789012!

Posted by Damon Ostrander on December 08, 2001 2:02 PM

Re: Interesting...must be a bug...

Mark and Lou,

One easy way to force Excel to interpret a number as text is to preceed it with an apostrophe character ("'") as you are typing it in. As you have found out, once Excel has interpreted the text as a being a numeric value, formatting the cell will not change this interpretation.

Damon