Help Excel SEARCH question.

[caLLous]

OK bear with me while I try to explain this.

I'm trying to get a formula together that looks in a string of text in a cell and returns certain values based on whether certain text strings are present in that string. I put something a bit pseudocodey together with SEARCH but it doesn't work. If you use IF with SEARCH, if the result comes back false (ie the specified string isn't in the text), SEARCH returns #VALUE! rather than FALSE.

(SEARCH returns the character position in the string of the search string so I'm guessing IF(SEARCH()>0,... is as good a way as any of confirming whether or not it's found the search string?)

So:

	A	B
1	xxx	=IF(SEARCH("x",A1)>0,"x","y")
2	yyy	=IF(SEARCH("x",A2)>0,"x","y")

The result in B1 is "x" but the result in B2 is #VALUE!.

Now, I did some looking around on tinter and I found something using ISNUMBER with SEARCH, like:

	A	B	
1	xxx	=IF(ISNUMBER(SEARCH("x",A1)>0,"x","y")
2	yyy	=IF(ISNUMBER(SEARCH("x",A2)>0,"x","y")
3	zzz	=IF(ISNUMBER(SEARCH("x",A3)>0,"x","y")

Which returns "x" for B1 and "y" for B2. BUT then B3 returns "y", which isn't what I want at all. If it finds ANYTHING except an "x" in the string it will output "y", where I need (at least) another layer of iteration for working with more than 2 options.

Ideally, the following would work (it should at least give you an idea of what I'm trying to achieve):

	A	B	
1	xxx	=IF(SEARCH("x",A1)>0,"x",IF(SEARCH("y",A1)>0,"y",IF(SEARCH("z",A1)>0,"z")))

but as I said, if "x" isn't in the string it will just return #VALUE! and not proceed to the 2nd and 3rd IF's.

Any ideas?

 

[caLLous]

I seem to have got it working by putting an ISNUMBER in for each SEARCH:

	A	B	
1	xxx	=IF(A1>0,IF(ISNUMBER(SEARCH("x",A1,1)),"x",IF(ISNUMBER(SEARCH("y",A1,1)),"y",IF(ISNUMBER(SEARCH("z",A1,1)),"z",""))),"")
2	yyy	=IF(A2>0,IF(ISNUMBER(SEARCH("x",A2,1)),"x",IF(ISNUMBER(SEARCH("y",A2,1)),"y",IF(ISNUMBER(SEARCH("z",A2,1)),"z",""))),"")
3	zzz	=IF(A3>0,IF(ISNUMBER(SEARCH("x",A3,1)),"x",IF(ISNUMBER(SEARCH("y",A3,1)),"y",IF(ISNUMBER(SEARCH("z",A3,1)),"z",""))),"")

So I get "x" for B1, "y" for B2 and "z" for B3 etc. Surely there is a more elegant way, though.

 

[Jupitus]

For text you could use 'find' instead.

=if(iserror(find("searchtext",CELL)),"it'snot there","it IS there")

or similar...

 

[caLLous]

Thanks Jup, I didn't know about ISERROR, how useful that would've been in past situations.

I ended up scrapping it altogether and going with a cleaner option (imo) - it's just to take domain names out of urls and react differently according to the domain so I'm just using MID and FIND etc to crop everything but the name (into a hidden column) and then IF(A1="domain","x",IF(A1="domain","y"etc etc

 

[Jupitus]

Thanks Jup, I didn't know about ISERROR, how useful that would've been in past situations.

I ended up scrapping it altogether and going with a cleaner option (imo) - it's just to take domain names out of urls and react differently according to the domain so I'm just using MID and FIND etc to crop everything but the name (into a hidden column) and then IF(A1="domain","x",IF(A1="domain","y"etc etc

Yep - that'll work and look a bit neater. Iserror can be very handy, you're right, hope you get use from the advice