Amend my Formula to ignore a possible space

[danbates]

Hi,

I have this formula:

=IF(ISERROR(SEARCH("16Cap",LOOKUP("zzzzzzz",C4))),"No","16Cap")

It works perfectly if the text in cell C4 reads 16Cap but if there is a space like this 16 Cap then it returns No.

Can my formula be altered so it can find both scenarios?

Thanks

Dan

 

[etaf]

=IF(ISERROR(SEARCH("16*Cap",LOOKUP("zzzzzzz",C4))),"No","16Cap")
Add the wildcard *
not sure i understand the formula , BUT using * will work for "16 Cap" or "16cap"
BUT also 16 123456789 cap or 16 [anything] cap
so maynot do what you want, as just not ignoring a space

 

[danbates]

That is just what I wanted.
Thank you etaf.
Dan

 

[etaf]

you are welcome , but as i said - its not just ignoring a space

 

[jtakw]

Hi,

I don't see the purpose of the LOOKUP in the formula, we're Not searching a Range of Cells, we're only searching in 1 cell, C4
This will Search for "Cap16" or "Cap 16" (with a space):

Book3.xlsx
	C	D
4	abc 16 Cap xyz	16Cap
Sheet886

Cell Formulas
Range	Formula
D4	D4	=IF(OR(ISNUMBER(SEARCH({"16Cap","16 Cap"},C4))),"16Cap","No")

 

[danbates]

Hi jtakw,

etaf said the same thing yesterday and supplied me with this formula before editing his/her thread.

=IF(ISERROR(SEARCH("16*Cap",C4)),"No","16Cap")

Your formula works as well jtakw so thank you.

thanks

Dan

 

[etaf]

Your formula works as well jtakw so thank you.

jtakw formula is much better as it is only looking for 2 possible entries {"16Cap","16 Cap"}
Whereas I'm using a wildcard - which will also see as true anything between 16 and CAP
so "16 AND CAP" = would be true as would anything between the 16 and the word cap
So you may get false readings from my formula. If it just happens to have other text or more spaces within 16, cap

 

[jtakw]

You're welcome danbates, and thanks @etaf

If we really want to isolate the Search strings, add a space fore and aft:

Book3.xlsx
	C	D
4	abc 16 Cap xyz	16Cap
5	16 Cap abc	16Cap
6	xyz 16 Captain	No
7	xyz 16 to 17 Cap	No
8	xyz 16 Cap	16Cap
Sheet885

Cell Formulas
Range	Formula
D4:D8	D4	=IF(OR(ISNUMBER(SEARCH({" 16Cap "," 16 Cap "}," "&C4&" "))),"16Cap","No")