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:
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:
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):
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?
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:
Code:
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:
Code:
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):
Code:
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?
