I have a column of cells containing text (column A), and I want to find a formula for the adjacent column (column B) that will put a 1 in column B if the adjacent cell in column A contains "XY".
I found this formula to count cells with specific text in them: =SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")
But I'm not sure how to do what I want.
Advice much appreciated!
Last edited by heathercoldsmith; 02-07-2011 at 09:13 AM.
Got it: =IF(ISERROR(SEARCH("*Text*",G2,1)),"0","1 ")
Okay, now what if I only want to count the cell if the text I'm interested in, "XY" stands alone rather than as part of a word?
Thanks, Robert. Another question...
I tried =IF(A2="XY",1,0), but that only works if "XY" is the only thing in the cell. I want to have a "1" in the adjacent column if the cell contains "XY" in the sentence. So if the cell contains "xy said blah blah" the adjacent column would have a 1, but if the cell said "roxy said blay blah" then the adjacent column would have a 0 because XY does not stand alone in the cell.Originally Posted by heathercoldsmith
Thanks again!
Heather
Hi there,
You can use =IF(A2="XY",1,0) in whatever cell you want and then fill (copy) it down as required and then sum the range or just =COUNTIF(A2:A10,"XY") for the entire range (change the range to suit).
HTH
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Try this:
=IF(LEFT(A2,3)= "xy ",1,IF(ISERROR(SEARCH(" xy ",A2)),0,1))
HTH
Robert
Or perhaps:
=ISNUMBER(SEARCH(" xy "," "&A1&" "))+0
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I tried Robert's formula, but I think I may have replaced text incorrectly and it didn't work. I went with the second one because it was simpler - works like a charm.
Thank you both!!
Heather
--deleted--- new post createdapologies
Last edited by martinlompa; 06-25-2015 at 02:44 AM.
Hi.
It's a forum rule that you start your own thread, rather than try to "hijack" someone else's thread. This helps prevent massive confusion arising -which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution). Also, preferably attach an Excel sheet. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!
Click on GO ADVANCED and use the paperclip icon to open the upload window.
The paperclip icon
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks