Finding exact text with blank space within a range

[oddbugg]

Hello,

I am looking for specific text that reads exactly "EX 03" in a column range, how do I indicate the space and the 03 within the quotation marks?


For example if I'm looking for text "EX" in a column C4:C12 (which has EX and other values like PM 01, IK 03, SA 08 etc.) and want to use the IF function:

=IF(C4:C12="EX","EX", "Non-EX") -> will return EX when it finds the exact text: EX. What if it finds EX 03 and I want the formula to accommodate this change i.e. still output an EX. I tried to use wildcards like * but it didn't work. Thanks for any help regarding this!

 

[pecoflyer]

Hi
Your syntax for the function is not correct. The IF function is tested on a single cell, ( or the result of another function), not a range.
For your problem you can use the SEARCH function ( among others) to find a result row by row
=IF(ISNUMBER(SEARCH("EX*",C4)),"EX","NOT EX") and pull down
If you need other possibilities with wildcards see https://exceljet.net/glossary/wildcard

 

[p45cal]

Re:

SEARCH("EX*",C4)

I made this 'error' once (or more) (it's not really an error); using the wildcard * in this way in SEARCH is not necessary because that's what SEARCH already does: look for the presence of EX within a longer string. The * wildcard looks for any character(s) or none. This gives the same result:
=IF(ISNUMBER(SEARCH("EX",C4)),"EX","NOT EX")

If you're looking for EX followed by at least 3 characters (space and 2 more characters) you could look for EX???, or even EX ??. This would exclude those strings which end in EX with nothing following:
=IF(ISNUMBER(SEARCH("EX ??",C4)),"EX","NOT EX")

If you have a version of Excel with dynamic arrays (formulae which spill their results into adjacent cells) you can put this formula into a single cell and it will spill down:
=IF(ISNUMBER(SEARCH("EX ??",C4:C12)),"EX","NOT EX")

[I think I've done this one to death]

 

[pecoflyer]

I made this 'error' once (or more) (it's not really an error); using the wildcard * in this way in SEARCH is not necessary because that's what SEARCH already does: look for the presence of EX within a longer string. The * wildcard looks for any character(s) or none. This gives the same result:

You are perfectly correct. Thanks for the heads up