Forum Home
Press F1
 
Thread ID: 46651 2004-07-01 05:01:00 XL formula B.M. (505) Press F1
Post ID Timestamp Content User
248821 2004-07-01 05:01:00 One for you XL gurus.

Let’s say cell A1 contains the colour of a paint but may contain something else. It could be any colour but always contains the word “paint”. Now what I want do is abbreviate the following formula so as, as long as paint appears in cell A1 one thing happens otherwise something else.

If (Or( a1=”blue paint”,a1=”pink paint”,a1 =”black paint”, and so on,1,100))

As you can see the colours are endless and I believe XL has a limit on the number of colours you can actually have in the OR function. So, what I’m looking for is a wild card if you like along the lines of IF(OR( a1=*.”paint”,1,100))

Hope that makes sense

Bob 		B.M. (505)
248822 2004-07-01 05:25:00 Hi Bob

If the first 5 characters are always"paint" then you can use :

if(left(a1,5)="paint", then x , else y)

or if the last 5 characters are always "paint" then you can use :

if(right(a1,5)="paint", then x , else y)

There is also a "mid" function but that won't help you here. I'm not sure if there is a "like" function but I'm sure someone will let you know if there is.

HTH

Andrew 		andrew93 (249)
248823 2004-07-01 06:20:00 your question got me thinking, "what if the word paint was in the middle of the string", and I have found an answer for you :

Use this formula :

=IF(ISERR(SEARCH("paint",A1)),2,1)

search is non-case sensitive and returns the starting position within the string contained in A1 of the word "paint"

if it can't find "paint" in A1 it produces an error - so we test for the error with ISERR and if it is an error then do number 2 (i.e. no paint)

If search finds "paint" then it isn't an error and so we do number 1 - I realise this is bacwards compared to the left and right functions I gave before but it works 		andrew93 (249)
248824 2004-07-01 09:01:00 Nearly Andrew,

I'm just getting a #value! at the moment but I'm working on it.

Cheers

Bob 		B.M. (505)
248825 2004-07-01 09:32:00 Hi Bob
If you want to post your formula then I will have a look at it.
Thanks
Andrew 		andrew93 (249)
248826 2004-07-01 23:36:00 Ok Andrew, this formula is what exists and works fine.

=IF(A6="","",IF(OR(E6="",AT6="",BB6="X"),0,(AT6-E6)*$CS$5)

And this is what we’re looking at adding.

IF(iserr(search(“paint”, AV6)),0,(AT6-E6)*$CS$5)

By simply adding I get #value!

I also tried dropping the iserr like this =IF(A6="","",IF(OR(E6="",AT6="",BB6="X"),0,IF(SEARCH("paint",AV6),0,(AT6-E6)*$CS$5))) and got #value!

XL does accept the formula though.

Cheers

Bob 		B.M. (505)
248827 2004-07-02 03:26:00 Hi

> I also tried dropping the iserr like this =IF(A6="","",IF(OR(E6="",AT6="",BB6="X"),0,IF(SEARCH("paint",AV6),0,(AT6-E6)*$C S$5)))and got #value!

A couple of observations - "paint" is in now in cell AV6 not A6 & there is a space in "$C S$5".

Assuming that if "paint" exists in AV6 the formula should = 0, and if it doesn't contain "paint" the formula = (AT6-E6)*$CS$5 then the formula should be
=IF(A6="","",IF(OR(E6="",AT6="",BB6="X"),0,IF(ISERR(SEARCH("paint",AV6)),(AT6-E6)*$CS$5,0)))

HTH 		Russell D (18)
248828 2004-07-02 04:44:00 That's it Russell, well done, I copied and pasted and away we went.

Personally I had never previously heard of the iserr and search functions so it's been a learning curve.

Thanks again to you and Andrew

Bob 		B.M. (505)
248829 2004-07-02 07:29:00 This is quite a nice little trick.
Someone should put it in a magazine or something.
robo. 		robo (205)
248830 2004-07-02 08:35:00 This from an amatuer xl user. I use xl a lot for doing pricelists etc. Where is a good place to go to learn the basics of writing formulas? I know the basics but would love to learn more.

cheers
Mike 		miknz (3731)
1 2 3