Using an array and looking for a substring

[planetdrouin]

I am trying to write an array formula which also looks for a substring.

Essentially one of my data fields could contain
"john 2003-4"

I want to be able to say look for "2003-4" in that column. My other parts were fine such as matching the date and using the first 5 letters of the name.

Any suggestions on how to do this?

 

[Zathras]

Without knowing more about your data layout, I would hope that you could use auto-filter and a custom selection exploiting the "contains" feature.

 

[KenWright]

What do you want to do with the data. Do you just want to know if it exists or what?? Assuming your list is in Col A, then:-

=MATCH("*2003-4*",$A$1:$A$100,0)

will give you the row number it is on if it exists.

If you don't like errors if it doesn't exist, then:-

=IF(ISNA(MATCH("*2003-4*",$A$1:$A$100,0)),"",MATCH("*2003-4*",$A$1:$A$100,0))

You need to give us some more details if you want anything else though

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------