Hello. I want to SEARCH two strings (str1 AND str2) inside a cell with text. Also, I want to apply the following operations:
- str1 OR str2
- str1 but not str2
I'd appreciate if you could help me with this. Thanks.
lakers_g3_1005.0059_1006.0059_49k.xlsx
B
C
D
E
F
G
1
tweet_text
"@Lakers"
"@MiamiHEAT"
"@Lakers " AND "@MiamiHeat "
"@Lakers" OR "@MiamiHEAT"
"@Lakers" but not "@MiamiHEAT"
2
Big props to @JimmyButler and the @MiamiHEAT , they had more fight tonight. Hopefully that gives us the eye of the tiger back and we come back ready for war in game 4. Let’s get this! @Lakers #LakeShow #ForKobe
This @Lakers team DOES NOT know how to maintain intensity without facing adversity. Frustrating. @MiamiHEAT They should just let the fans know they don't plan on playing, and we can just tune into the following game.
@Lakers are playing like they don’t want to win this game. @JimmyButler leaving everything out on the floor. We are going to need more from @KingJames and @AntDavis23
@lakers Give the Heat credit, they played their hearts out. They deserved to win. We didn't have the same energy and effort. Rest, make adjustments for next game and be ready to play!!!
Big props to @JimmyButler and the @MiamiHEAT , they had more fight tonight. Hopefully that gives us the eye of the tiger back and we come back ready for war in game 4. Let’s get this! @Lakers #LakeShow #ForKobe
This @Lakers team DOES NOT know how to maintain intensity without facing adversity. Frustrating. @MiamiHEAT They should just let the fans know they don't plan on playing, and we can just tune into the following game.
@Lakers are playing like they don’t want to win this game. @JimmyButler leaving everything out on the floor. We are going to need more from @KingJames and @AntDavis23
@lakers Give the Heat credit, they played their hearts out. They deserved to win. We didn't have the same energy and effort. Rest, make adjustments for next game and be ready to play!!!
Big props to @JimmyButler and the @MiamiHEAT , they had more fight tonight. Hopefully that gives us the eye of the tiger back and we come back ready for war in game 4. Let’s get this! @Lakers #LakeShow #ForKobe
This @Lakers team DOES NOT know how to maintain intensity without facing adversity. Frustrating. @MiamiHEAT They should just let the fans know they don't plan on playing, and we can just tune into the following game.
@Lakers are playing like they don’t want to win this game. @JimmyButler leaving everything out on the floor. We are going to need more from @KingJames and @AntDavis23
@lakers Give the Heat credit, they played their hearts out. They deserved to win. We didn't have the same energy and effort. Rest, make adjustments for next game and be ready to play!!!
Big props to @JimmyButler and the @MiamiHEAT , they had more fight tonight. Hopefully that gives us the eye of the tiger back and we come back ready for war in game 4. Let’s get this! @Lakers #LakeShow #ForKobe
This @Lakers team DOES NOT know how to maintain intensity without facing adversity. Frustrating. @MiamiHEAT They should just let the fans know they don't plan on playing, and we can just tune into the following game.
@Lakers are playing like they don’t want to win this game. @JimmyButler leaving everything out on the floor. We are going to need more from @KingJames and @AntDavis23
@lakers Give the Heat credit, they played their hearts out. They deserved to win. We didn't have the same energy and effort. Rest, make adjustments for next game and be ready to play!!!
Thanks for the help, aRandomHelper. I am wondering how implement nesting one command inside the other? Is it by hit and trial or do you master individual commands and then attempt nesting?
I usually go about forming formulas in pieces, so this would be my approach.
For the case of AND() and OR(), I would make sure each component work correctly before inserting them.
If it's going to be a really long nesting of IFs, sometimes I do it on a notepad first, spreading the formula across multiple lines for visibility's sake, then form it up and throw it into Excel. Probably sounds weird but it works for me.
If Lakers is found, SEARCH will produce a single number and COUNT will count that number which would produce a 1.
If MiamiHEAT is not found, SEARCH will produce an error so COUNT will return 0 (zero).
The formula concatenates (as a string) the two relevant values which would produce "10"
If both Lakers & Miami were found the result would be "11" (so we could have used a formula like this for column E as well )
If neither found the string would be "00"
and if Lakers not found but Miami is then "01"
It was a leftover from copying the E2 formula, but it still works.
I was hoping one of your requests was an "either but not both" so I could use XOR.
I don't think I've ever used that in Excel, but I remember using it almost 50 years ago in Assembler.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.