EXCEL 2007 reading E as power of ten

[DoubleZ]

I have a worksheet that will have data entered into it that will sometimes
contain an "E" (e.g. 727E+10). The problem is, EXCEL essentially reads this
as 727*10^10. I know that by formatting the cell to text will fix this.
However, some of the data that is entered will just be numbers (without any
text) and I have formulas that calculate differently based upon whether the
input is a number or text. So, I can't really change all the cells to text
because it will throw off my formulas and I don't want the people entering
the data to have to go through and individually change all the cells
containing "E" to text. Is there a setting I can change that will stop Excel
from reading and E as something to the tenth power?

Thanks.

 

[Bob Phillips]

You can either enter that data with a leading apostrophe, '727E+10 or if you
format the column as text, you should be able to change the other formulae
to accommodate this. Give an example and we'll show you how, as well as an
example where you do different things depending upon whether it is text or
not.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[DoubleZ]

Here is my example. Please excuse th ridiculously long formulas, I seem to
have a fear of VBA.

Cell A3 contains the input data. Cell B3 contains the formula:

=IF(A3="","",IF(ISTEXT(A3),D3,IF(AND(A3>=130,
A3<=720),A3-120,IF(AND(A3>=740, A3<=950),A3,IF(AND(A3>=970,
A3<=1700),A3+120,"ERROR")))))

So this goes to cell D1 if the input contains text (e.g. 720A+09) or it
returns a certain value if the input is a number. If A3 contains text, then
C3 is:

=IF(ISTEXT(A3),VALUE(LEFT(A3,LEN(A3)-4))+VALUE(RIGHT(A3,LEN(A3)-5)),"")

This is just a reference cell for D3 because D3 is so long already. D3 is:

=IF(ISTEXT(A3),IF(ISNUMBER(SEARCH("720A",A3)),C3-100,IF(ISNUMBER(SEARCH("720B",A3)),C3-80,IF(ISNUMBER(SEARCH("720C",A3)),C3-60,IF(ISNUMBER(SEARCH("720D",A3)),C3-40,IF(ISNUMBER(SEARCH("720E",A3)),C3-20,IF(ISNUMBER(SEARCH("720F",A3)),C3,IF(ISNUMBER(SEARCH("950A",A3)),C3+20,IF(ISNUMBER(SEARCH("950B",A3)),C3+40,IF(ISNUMBER(SEARCH("950C",A3)),C3+60,IF(ISNUMBER(SEARCH("950D",A3)),C3+80,IF(ISNUMBER(SEARCH("950E",A3)),C3+100,IF(ISNUMBER(SEARCH("950F",A3)),C3+120,"ERROR")))))))))))),"")

This just searches through A3 to see which letter it contains and returns a
particular value based upon that.

I think because of my formula in C3 it will be difficult to add the ' in
front of the value containing an E. I know I could add an ' in front of all
the input values and change the values being subtracted in C3, but I won't be
the one inputting the data and I fear that adding an ' wont be greeted with
open arms.

Sorry for the book. Thanks for your help.