I have a Google spreadsheet. One of the columns in the spreadsheet is "Subject". It contains rows like - "15 placebo St", "5 Guatemala Ave", "I am really Interested", "Testing", "Rental or Rent".
I wanna make a column besides this "Subject" column called "tags". The tag would be "buyer" or "false". I want to write an if statement that checks the following condition: #If a row contains an address (has a number somewhere in the cell and/or has the word "St", "Street", "Ave") OR if the subject line has the word "interested" then tag it as a "Buyer". Else "False"
How do I put this as one excel formula?
CodePudding user response：
This sounds like you are wanting to know how to keep it all straight in your head!
- Don't even try.
- Consolidate.
Step 1: Don't try to be clever and do it all in one column.
- Create one column per test criterion that returns 0 if that row doesn't meet the criteria, and 1 if it does.
- Then, create yet another column that is the
SUM()of your criterion columns. This gives you a score. - Then, if column W is that score, X2's formula can be
=IF(W2=0, FALSE, "Buyer")and apply that formula for all of column X.
Step 2: Okay, so now you have your answer by breaking it into several steps, and you can hide those criterion columns. I usually move all the columns I want to hide to the right, so I have a continuous block of visible columns, followed by all the hidden columns. But if you still want to figure out how to do it all in a single column, it shouldn't be out of reach now that you've broken down and tested all your smaller steps! Now you just have to put it all together. Just be sure to do it in Yet Another Column. That way your work in step one can serve to verify you did it right before you delete all those columns you made in step 1.
But I recommend stopping after step 1, because what you want is wrong.