Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
EXCEL 2007 reading E as power of ten
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
EXCEL 2007 reading E as power of ten
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
EXCEL 2007 reading E as power of ten
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(S EARCH("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(SEA RCH("950E",A3)),C3+100,IF(ISNUMBER(SEARCH("950F",A 3)),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. "Bob Phillips" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop Excel reading 01/02 as 1st February 2007 | Excel Discussion (Misc queries) | |||
No ActiveX controls when reading 2007 file formats into Excel 2003 | Excel Discussion (Misc queries) | |||
link Excel / Power Point | Excel Discussion (Misc queries) | |||
How do I graph y=2 to the power of x in excel | Excel Discussion (Misc queries) | |||
pie charting through excel, using power point | New Users to Excel |