Ignore textual values in Excel formula...

[sunil5]

Hi all,

As the subject describes, I would like to know how I can ignore text in a cell that consitutes part of a formula.

As most of you would be aware, if a text value is entered where, for example a 'time' value is entered, #VALUE is displayed.

What I need is the formula still to work- by 'ignoring' the text value(s) and continue with the calculation.

Any help on this would be much appreciated.


Thanks,

Sunil

 

[SkipVought]

Hi,

Use IsNumber(reference)
[tt]
=if(isnumber(a1),....)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[bluedragon2]

It depends on what the data looks like. You can use a combination of functions to ignore part of the cell. Do you have any example data?

For example, if I want the value 12 from a cell containing:

Soccer Balls - 12

I would use =VALUE(RIGHT(A1,2))

There are many ways, but you would need some kind of standard of data entry. You can almost always pick out what you want in a cell, but if that data fluctuates, then so will your formula.

Give some example data and we will see what we can do.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[byundt]

A different approach would be to use a Custom number format so the cell contains only a number, but is displayed with text before or after the number. In so doing, your formulas don't need to be changed at all.

You do this from the Format...Cells...Number menu item. Choose Custom on the left, then enter a format string like this:
#" hours"
If the cell contains 8, it would be displayed (and print) as "8 hours". In the custom format string, the # is the normal formatting characters that you see. The part in quotes following it is a text constant that is added after the number.

 

[sunil5]

Thanks for all the replies- I cant try any of the suggestions until im at work in a few hours...

Anyway here is some example data:

A1 B1 C1 D1 E1
10:00 15:00 OFF 20:00 "Total"


In the example above, the values represent times for for an employee - a timesheet. So what I want to do, lets say for example, SUM up the total hours- A11 = E1 (actual formula is a far more complex). Now I would want this formula to work as normal even though the value in C1 is text value (OFF). Of course any standard formula would normally result in #VALUE error when there is an 'invalid' entry.

Basically any cell that is to contain text will ONLY have text in it and not both numbers and letters.

The text value can be any of the following: A/L, B/H, OFF

Hope this makes things a bit more clearer.

I will try the suggestions above soon.

Again many thanks,

Sunil

 

[byundt]

Sunil,
SUM ignores text values when it adds up the numbers--in both Excel 97 and 2003. SUM will therefore return the correct total without an error.

=A1+B1+C1+D1 does return an error, however, if A1=1, B1=2, C1="OFF" and D1=3
Brad

 

[sunil5]

Hi byundt- thanks for replying...

Yeah I realise that SUM doesn't result in an error. But the formula that i am using is a fairly complex IF statement and this does result in an error.

Sunil

 

[mscallisto]

Another Option is to test for the error first and do the calc after determining all is well.

Example Either show an error message or do the calc
If ISERROR(A2-A1),"Error",A2-A1)

or show no error message or do the calc
If ISERROR(A2-A1),"",A2-A1)

 

[byundt]

Sunil,
Could you post the formula that gives you trouble? As well as the cell address that contains text?
Brad