IF contains abc formula help.

Microsoft Excel 2003 (full product)
July 29, 2011 at 03:47:38
Specs: N/A, N/A
IF <cell> contains "abc" formula help needed.

What I want to do is to create a formula that looks for a string within the text of a cell, for example, I want the "B" column to give an output that enables a formula (that I will write later) to calculate how many parts are available in a certain depot and region.

The first step I want to take is to get it to count how many of this part are in the "Defective" warehouses, so that I can then exclude those from the other counters...

Cell B3 currently has a formula of:
=if(A3="","",if(A3="London","EMEA",if(A3="DEFECTIVE","Defective","USA")))

To make it easier to see/understand, I've copied it to a new file and shared that out:

https://spreadsheets.google.com/spr...


All help gratefully received. :o)


See More: IF contains abc formula help.

Report •

#1
July 29, 2011 at 04:09:07
Actually, a bit more Googlefu has brought up a solution that works...modifried to suit, this would equate to something not too far from:
=IF(ISNUMBER(SEARCH("defective",A3)),"Defective",IF(ISNUMBER(SEARCH("london",A3)),"EMEA",""))

I just need to finish adding more IF statements to that, and then fill...


Report •

#2
July 29, 2011 at 04:10:09
Why can't I mark this as solved, when I've solved it myself..??

/me is confusticated....


Report •

#3
July 29, 2011 at 04:30:11
Okay, that doesn't seem to quite be what I want to do....unless I wanted to write a whole string of IF statements...

I tried to change it to also use the AND or OR statement - so that I could say if <cell> contains "London", "Dresden" or "Sweden", then display "EMEA"; rather than having a whole IF string for each of them individually..but this doesn't seem to have worked.. :o(


As it stands, the formula is:
=if(A3="","",if(ISNUMBER(SEARCH("defective",A3)),"Defective",IF(ISNUMBER(SEARCH(or("london","dresden","sweden"),A3)),"EMEA",IF(ISNUMBER(SEARCH("Auckland",A3)),"APAC","USA"))))

When I tested that without the OR section, it was working...
=if(A29="","",if(ISNUMBER(SEARCH("defective",A29)),"Defective",IF(ISNUMBER(SEARCH("london",A29)),"EMEA",IF(ISNUMBER(SEARCH("Auckland",A29)),"APAC","USA"))))


Any tips/help..?


Report •

Related Solutions

#4
August 3, 2011 at 02:02:33
No ideas..?

Report •

#5
August 5, 2011 at 04:03:00
Never mind...was able to work it out myself..

Turns out that GDocs spreadies aren't limited to only able to use seven IF statements in a single formula..


Report •

Ask Question