Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search/Extract Data w/in Text File | Excel Discussion (Misc queries) |