IF OR and ISTEXT Formula

[Chialiwa]

I'm getting a #SPILL error on my formula and I'm not sure what I'm doing wrong.

=IF(ISTEXT(SEARCH("Organization",B2)),MID(B2,17,3),IF(ISTEXT(SEARCH({"ELKTON","ORLAND","ROCKYH"},B2)),LEFT(B2,6),A1))

What I want is for the formula to check in cell B2 for the word "organization", and if it contains that word, give me the the specified characters in my MID formula, but if the words ELKTON, ORLAND, or ROCKYH are in B2, then I want it to return the 6 characters on the left. If if doesn't have any of those words/meet those conditions, I want it to return the value in A1. What am I doing wrong?

 

[Peter_SSs]

See if this does what you want

=IF(ISNUMBER(SEARCH("Organization",B2)),MID(B2,17,3),IF(COUNT(SEARCH({"ELKTON","ORLAND","ROCKYH"},B2)),LEFT(B2,6),A1))

 

[Chialiwa]

See if this does what you want

=IF(ISNUMBER(SEARCH("Organization",B2)),MID(B2,17,3),IF(COUNT(SEARCH({"ELKTON","ORLAND","ROCKYH"},B2)),LEFT(B2,6),A1))

Peter, this worked perfectly! why isnumber and count instead of istext? I would not have thought to use count at all. This totally worked for me, thank you so much!

 

[Peter_SSs]

Peter, this worked perfectly!

Good news.

why isnumber ... instead of istext?

Because SEARCH will never return text, it either returns a number or an error value. From Help on the SEARCH function..

The SEARCH function locates one text string within a second text string, and returns the number of the starting position ..

If the value ... is not found, the #VALUE! error value is returned.

why .... count instead of istext?

Same argument as above about why not istext. But also ..
SEARCH({"ELKTON","ORLAND","ROCKYH"},B2)
This does three SEARCH functions so it will return three values and each of those values will be a number or an error. COUNT is used as it will count how many numbers are returned from those three searches & ignore the error values. If the COUNT is 1, 2 or 3 then at least one of the words is found so the left 6 characters will be returned. If the three searches all return an error value then COUNT will be 0, which is treated as a FALSE in the IF function, so A1 would be returned.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)