The SEARCH function and parsing data
 

Hey guys. I have a question regarding the SEARCH function.

I am trying to parse through data in an excel spreadsheet in order to look
for certain characters. Unfortunately, I have to use excel functions and not
a Visual Basic script :-( ).

I need to check separately if it has letters, dashes, slashes, etc. and mark
it appropriately. So I have one column that is the data, then separate
columns for alpha characters, dashes, slashes, and miscellaneous. I can check
for slashes and dashes easily since it is only a few chars... and I can check
for alpha chars... but the entry would be ridiculously long...

=IF(OR(ISERROR(SEARCH("a",C15)),ISERROR(SEARCH("b" ,C15)),
ISERROR(SEARCH("c",C15)),ISERROR(SEARCH("d",C15)), .......),"no","YES")

Is there a way to put a range of values in the SEARCH function?

Or does anyone know a simplier way to do this? other than writing a script
in VB?

I really appreciate any assistance you could offer! Thanks!

-Michael

try
=IF(AND(ISERROR(SEARCH({"A","B","C"},A1))),1,0)

"Michael" wrote:

Hey guys. I have a question regarding the SEARCH function.

I am trying to parse through data in an excel spreadsheet in order to look
for certain characters. Unfortunately, I have to use excel functions and not
a Visual Basic script :-( ).

I need to check separately if it has letters, dashes, slashes, etc. and mark
it appropriately. So I have one column that is the data, then separate
columns for alpha characters, dashes, slashes, and miscellaneous. I can check
for slashes and dashes easily since it is only a few chars... and I can check
for alpha chars... but the entry would be ridiculously long...

=IF(OR(ISERROR(SEARCH("a",C15)),ISERROR(SEARCH("b" ,C15)),
ISERROR(SEARCH("c",C15)),ISERROR(SEARCH("d",C15)), .......),"no","YES")

Is there a way to put a range of values in the SEARCH function?

Or does anyone know a simplier way to do this? other than writing a script
in VB?

I really appreciate any assistance you could offer! Thanks!

-Michael

Try...

=IF(OR(ISNUMBER(SEARCH({"a","b","c","d"},A1))),"Ye s","No")

or

=IF(OR(ISNUMBER(SEARCH(B1:B4,A1))),"Yes","No")

....where B1:B4 contains your list of letters to check. Note that the
second formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!

In article ,
"Michael" wrote:

Hey guys. I have a question regarding the SEARCH function.

I am trying to parse through data in an excel spreadsheet in order to look
for certain characters. Unfortunately, I have to use excel functions and not
a Visual Basic script :-( ).

I need to check separately if it has letters, dashes, slashes, etc. and mark
it appropriately. So I have one column that is the data, then separate
columns for alpha characters, dashes, slashes, and miscellaneous. I can check
for slashes and dashes easily since it is only a few chars... and I can check
for alpha chars... but the entry would be ridiculously long...

=IF(OR(ISERROR(SEARCH("a",C15)),ISERROR(SEARCH("b" ,C15)),
ISERROR(SEARCH("c",C15)),ISERROR(SEARCH("d",C15)), .......),"no","YES")

Is there a way to put a range of values in the SEARCH function?

Or does anyone know a simplier way to do this? other than writing a script
in VB?

I really appreciate any assistance you could offer! Thanks!

-Michael