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. |
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. |
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. |
All times are GMT +1. The time now is 10:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com