Hi everyone
I'm trying to sort through a large text file, containing multiple cells. I'm using the function =IF(ISNUMBER(SEARCH("*hit*",G5)),"1","0"). This returns 1 if the text in G5 contains the string "hit". It works fine, but only if I do it on one cell, and I'm trying to do the search in multiple cells.
Is there a way to do that?
Thank you!
hit.xlsx
Please see attached
Array formula
=SUM(--(ISNUMBER(SEARCH("hit",$G$4:$G$30))))
This will count how many times this word is within your column
Last edited by RobertMika; 11-21-2013 at 12:59 AM.
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
T - welcome to the forum. What output are you looking for: the count of "hit" occurrences in a range or the address of cells with "hit" in them?
Cheers
<-- If you're happy & you know it...click the star.:-)
Thank you!
I just want to know whether or not the string "hit" appears anywhere in those cells. I want it to return 1 if it appears, 0 if not.
maybe also countif ???
=--(COUNTIF(A1:A10,"*hit*")>0)
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
welcome to the forum, Tamar. SEARCH is actually already looking for partial text, so that's why you don't need the wildcards. 1 & 0 are numbers, so you don't need the double quotes too.
=IF(ISNUMBER(SEARCH("hit",G5)),1,0)
and if you want to do a range & don't wish to press CTRL + SHIFT + ENTER, you could use:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("hit",$G$4:$G$30))))>0,1,0)
but actually COUNTIF will do. now you need the wildcards:
=IF(COUNTIF($G$4:$G$30,"*hit*"),1,0)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
I've used Vlady method and it seems to work! Thank you!
Just a quick follow up question - if I need to o the same, but with "and" or "or" - meaning, return 1 if somewhere in those next 10 cells there is "hit" or "direct", or return one if there is "hit" and "direct" - how do I do that?
Thanks again!
=IF(SUM(COUNTIF($G$4:$G$30,{"*hit*","*direct*"})),1,0)
Thank you! Will that give me "or" or "and"?
thank Robert
also you can try the doule - to turn true/false to 1/0
=--(SUM(COUNTIF(A1:A9,{"*hit*","*direct*"}))>0)
This is the AND.
If you cell has both values "hit" and "direct" use just this part
=--(SUM(COUNTIF(A1:A9,{"*hit*","*direct*"}))
to get the overall count.
Thank you so much y'all!
One more question - if I want - for another use - that excel will copy the whole cell to another location if it contains (not equal!) a specific string - e.g., I have coco_hit_5 and I want to copy all of it since it contains "hit" - how do I do that? I tried doing it with advanced filter but I don't want it to effect the row, just specific cells.
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks