If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
Ads 
#22




How do I get a value instead of an error or txt in an Excel ce
Hi I am trying to figure out a formula that allow me to avoid sign
carachters such "#< $ % &" being enter as data what can I do? Help me! "Shane Devenshire" wrote: > Hi, > > 1. First let me simplify one of the formulas > > =IF(B1,A1/B1,0) > > 2. If the cells really contain trailing spaces you can remove all of them > with a single command  highlight all the offending cells and press Ctrl+H, > enter three spaces in the Find what box and leave the Replace with box > empty. Then click Replace all. The cells will still be text most likely, > but they will not contain 3 trailing spaces. > > 3. You can use the VALUE function to convert text numbers followed by > trailing spaces to numbers =VALUE(A1) However, this might not work if the > trailing spaces were not really spacebar spaces. In which case you could > try =VALUE(TRIM(CLEAN(A1))) > > 4. However, maybe the following solution will be best: > > Problem: > When numbers are enter as text they may not calculate within formulas as > they should. A few formulas will work fine despite the numbers being > entered as text. Numbers can be stored as text by 1. preformatting the cell > to Text and entering the number, 2. Typing an apostrophy in front of the > number '123, 3. Because the data was downloaded from a soure inwhich it was > stored as a text number, 4. Because you used the Text to Columns command and > converted it to text., and.... > > There is no sure indicator that a number is stored as text, although numbers > are usually right aligned and text left aligned, this may not be the case. > If you are using a later version of Excel, Error Checking green triangles > may appear at the top left corner of these cell, but this feature may be off > or the version of Excel may not support it. (2000 and earlier). > > You can find out what data type the entries are by using the =ISTEXT(A1) or > =ISNUMBER(A1) functions. You can not tell by checking the Format. If a > number was entered in a cell preformatted as General or as a number, then it > will be a number, even if it's current format is Text. Likewise a number > entered in a cell preformatted as Text will be text even if it's current > format is Number, General, Date, Currency and the like. > > Solution: > 1. Change the format to one that is numeric and then reenter the numbers > (too slow and error prone.) > 2. Select the cells and open the Error Checking options and choose Convert > to Numbers. > 3. Select an empty cell and copy it. Select the text number cell and choose > Edit, Paste Special, Add (or Subtract). This method is ~100 times faster > than #2. > > Dates are numbers, and if they are stored as text, you will not get an Error > Checking triangle, so method #3 is obligatory if there is a substantial > number of dates to convert. > > If this information is helpful, please click the Yes button. > > Cheers, > Shane Devenshire > > "lypolintan74" > wrote in message > ... > > > > > > "T. Valko" wrote: > > > >> > You can install this macro, select the import and run the macro > >> > >> You forgot the macro! > >> > >> Did you mean this one? > >> > >> http://www.mvps.org/dmcritchie/excel/join.htm#trimall > >> > >>  > >> Biff > >> Microsoft Excel MVP > >> > >> > >> "Peo Sjoblom" > wrote in message > >> ... > >> > It's not a good way of handling DIV errors because it will hide other > >> > errors > >> > > >> > It's better to use > >> > > >> > =IF(B1=0,0,A1/B1) > >> > > >> > > >> > You can install this macro, select the import and run the macro > >> > > >> >  > >> > > >> > > >> > Regards, > >> > > >> > > >> > Peo Sjoblom > >> > > >> > "LHW" > wrote in message > >> > ... > >> >> > >> >> I understand how the ISERROR() function handles division by zero in > >> >> your > >> >> example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to > >> >> the > >> >> numeral 0. However, the problem I have encountered is that numbers > >> >> are > >> >> downloaded as text with three trailing spaces. I have to manually > >> >> remove > >> >> these trailing spaces to use these numbers in the calculation. This > >> >> is a > >> >> time consuming task. > >> >> > >> >> Is there a better way to automatically convert numbers stored as text > >> >> to > >> >> numeric format? I suppose I could write a macro to automatically > >> >> remove > >> >> three characters from the end of each item in a onedimensional array > >> >> (in > >> >> my > >> >> case), but I've never written a macro in Excel 2007. Can the VALUE > >> >> function > >> >> not be used to convert text to numbers? I have had no luck using it > >> >> to > >> >> do so. > >> >> > >> >> Any help would be greatly appreciated. > >> >> > >> >> Best wishes, > >> >> > >> >> Lewis Williams > >> >> > >> >> "JLatham" wrote: > >> >> > >> >>> "wrap" your formula inside of an IF statement with a test to see if > >> >>> it > >> >>> creates an error or not. Consider this > >> >>> > >> >>> A B C > >> >>> 1 5 0 =A1/B1 > >> >>> will give you a #DIV/0 error > >> >>> > >> >>> But if you do this at C1: > >> >>> =IF(ISERROR(A1/B1),"invalid",A1/B1) > >> >>> you would be shown 'invalid' in C1 but if B1 is a nonzero value > >> >>> then > >> >>> you'd > >> >>> get the true answer. You can substitute a value for "invalid" such > >> >>> as > >> >>> zero > >> >>> or one, like: > >> >>> =IF(ISERROR(A1/B1),0,A1/B1) > >> >>> > >> >>> The ISERROR() test will handle all errors, there are other options > >> >>> such > >> >>> as > >> >>> ISERR() handles any error except #N/A > >> >>> while ISNA() only handles #N/A  good for use with VLookup and > >> >>> Hlookup > >> >>> operations. > >> >>> > >> >>> "SkiRJB" wrote: > >> >>> > >> >>> > Is there a way to get a formula to return a value instead of an > >> >>> > error > >> >>> > (#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get > >> >>> > a > >> >>> > value > >> >>> > at some where other than zero? > >> > > >> > > >> > >> > >> > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
resetting last cell  jagdish.eashwar  Excel Discussion (Misc queries)  11  March 31st 06 02:06 AM 
missing cell borders in Excel  Nak  Excel Discussion (Misc queries)  7  March 9th 06 03:54 AM 
Possible Lookup Table  Karen  Excel Worksheet Functions  5  June 8th 05 09:43 PM 
Copy cell format to cell on another worksht and update automatical  kevinm  Excel Worksheet Functions  21  May 19th 05 11:07 AM 
Paste rows of numbers from Word into single Excel cell  BecG  Excel Discussion (Misc queries)  1  December 8th 04 04:55 PM 