cell format question

[George Applegate]

What causes this:

Format a column as text
Enter into first cell 1257470
then next cell 12574700
then next cell 125747000
then next cell 1257470000
then next cell 12574700000
then next cell 125747000000, but it changes it to 1.25747E+11

What is going on with this???

Also, in a text cell, I enter

08-812000-02
then, go to edit replace and try to replace the '-' with nothing
but when it does, it changes the cell to 881200002 - it takes the
dashes, but it also takes off the leading zero???

WHY?

Any help with these two questions would be greatly appreciated.
ga

George Applegate
(e-mail address removed)

 

[Bernard Liengme]

I cannot repeat your first problem. As long as I have formatted the cell as
text I do not get scientific notation. If the cell is formatted as text then
anything you type in it should be left aligned. Also if the text is in A1
then this formula in another cell =ISTEXT(A1) should return TRUE

I can confirm the second problem. If I type 08-812000-02 in A1 the formula
=ISTEXT(A1) in B1 returns true. But when I do the find and replace I get
FALSE and the leading zero is gone.
One workaround: in another cell enter =SUBSTITUTE(A1, "-","") to get
088120002. The use Copy followed by Edit|Paste Special->Value to change the
entry from a formula to a value.
any help?

 

[Nick Hodge]

George

As soon as Excel gets near to it's 15 digit limit it will endeavour to
'convert' it to scientific notation

Formatting the cell in advance will stop this happening

In the latter case, changing the dashes to nothing makes Excel think they
are now numbers and don't therefore need a leading zero

To overcome both, try putting a leading apostrophe in front, it won't show,
but will be treated as text

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk