Excel IF Function: If Cell Contains Specific Text - Partial Match IF Formula
ฝัง
- เผยแพร่เมื่อ 28 ส.ค. 2024
- In this video we use the IF function of Excel to test if a cell contains specific text.
Usually with the IF function you test a cell for a complete match, but in this example we need a partial match on text.
We then extend the scenario to handle a problem and then test for two different specific text strings.
Advanced Excel Formulas book (840 pages and more than 150 Excel functions)
amzn.to/3Rg87Go
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
Advanced Excel Formulas book (840 pages and more than 150 Excel functions) amzn.to/3Rg87Go
FINALLY!!! This is the IF function (partial match) I've been wondering about and looking for about a year! THANK YOU!
Glad I could help, Tony 👍
It's unreal how MS Excel has not created a simple" If (Contains ......)" function. Your tutorial really helped me through hours of figuring it out.
Great! And yes, that is a surprise. But it would dent our fun 😂
i just wanna let you know this video literally saved me! i was going to check and key in one by one...thank you so much!!!
Glad I could help, Gwen.
Gggwt
Evans wnwsmwwwwbw
Whwkw@@Computergaga gassbdjdcdd scwcsw
Mann,almost got stuck for a whole week finding a short way to summarize this solution. But at last,something tickled in my mind,and relevant keywords took me to your video. Thanks a lot brother,you saved my almost,3-4 hours 😂❤
You're welcome! Thank you for the comments, Kashif.
That helped a lot, thanks - super glad you added the OR Function in the last part.
What if you want to add a third (or more) OR function - does that work?
Sure. You can add as many conditions to OR as you require.
Happy that yours was the first video that came up in my search. Liked they way you Zoomed in while typing the formula. Really thoughtful. Thank you so much.
Awesome, thank you!
Hi Alan.. great tips on the use of FIND and SEARCH for partial match. Just because I've got my head in your VBA course, I attempted to write a User Defined Function to do the same using InStr(). See below. I tested it and it seems to work. The user can define the search text directly in the function argument with quotes or point to a cell reference to make it dynamic. Credit to you for your excellent VBA course. A few months ago, I wouldn't have even known where to begin writing a UDF, let alone being able to complete one without assistance.. so.. making progress. Thanks and thumbs up!
Function Find_Partial_Match(CelRef As Range, Text As String) As String
If InStr(CelRef, Text) = 0 Then
Find_Partial_Match = "No Match"
Else
Find_Partial_Match = "Match Found"
End If
End Function
Awesome work Wayne.
Thank you!! Your video helped me after 8 hours on this assignment.
Happy to help Princess K.
Thanks for the tutorial; Excellent Explanation. Was the first tutorial I've encountered not full of Jargon that leaves my head spinning
Great! Thank you.
Ahhhh!!! It worked for me 🎉🎉🎉 Just wanted to say Thank you so much for taking your time to teach. This video helped me immensely. Liked and subscribed ❤❤❤❤
That's great! Thank you for taking the time to comment. I'm so glad that it helped.
THANK YOU!!.. I was working in a search formula that did work but you help me adding up the ISNUMBER to it.. Now it works!!
Excellent! You're welcome.
One of the best videos in excel
Thank you very much 😊
Great video. Do you have an example where if you have a text list that you want to add a specific value in a cell next the text if it is visible. For example everywhere I have the category “apples” I want to assign it the code 123 or if “oranges” is visible in a cell assign it “456”?
This is the formula that I'm looking for. Thanks
Awesome! Glad I could help.
Thank you so much for making this video. You have save a lot of my time.
I think i am going to take some time and watch all of your video to lern excel.
You're welcome Ramashry. I hope I can help more in further videos.
Thank you! Thank you! I scoured the internet looking for something to help me today and you did!
Great! Happy to help, Crystal.
Excellent explanation. I solved the problem I had. Thank you very much "indeed".
I liked the way you explain, especially for your accent!
From now on, I will come to your channel in case I have troubles with Excel. Also, I'll tell my friends about your Channel.
I studied English in Cambridge, UK, a long time ago, and I always loved the British Accent.
Now I live in USA, and the American English sounds like another language. (No offense, please)
Thank you very much. Happy to have helped.
Thank you for your comments.
Thats really great!!, i use something like tjis all the time, but, i use the countif function, using another cell to match with, and space like you do, and i use "*" cell "*" i use it to find clues in what i must look for next.
I like the way you teach, your very fun
Thank you William.
Yes, a great a use of wildcards with COUNTIF for partial text matches.
@@Computergaga please make a video of this trick too, using wild card for search
Got one here my friend - th-cam.com/video/TlyMhnsWioQ/w-d-xo.html
You're a lifesaver!
Awesome!
REAL Time Saver - Thank you
You're welcome. Thank you.
Thank you thank you thank you! I knew there had to be a way to do this. Used this method for IFS, and added a final clause for TRUE,"default" if all the previous tests were false.
Excellent! No problem.
Great tutorial. You have the gift of teaching!
Thank you very much 😊
Exactly what I was looking for!!! Thanks so much!
You're very welcome, Jan. Great to hear.
Thank you, this was very easy to understand. It made my work easier.. Thank you for explaining it the simplest way possible
You're very welcome. Thank you.
Thank you for this video. Very clear and detailed. Very useful and time saving video. Again, thank you for taking the time to post this video.
You're very welcome. Thank you.
Thank you so much. You really saved my time. I was looking for something like this. It's so useful for sorting from a large data.
You're welcome, Akash. Happy to help.
Thank you! Have been looking for this! :)
No problem!
Excellent clear explanation, thanks Alan
You're very welcome Glenn.
This formula is the most powerful and great example.
Thank you Abhishek.
This helped, thanks a lot. Is there a way to add a third argument in? Let's say you want the answers to be local, far and don't visit based off the postcodes. Is this something even possible?
Was struggling doing mannual changes everytime.. this saves a lot of time... thanks a lot
You're welcome, Jyoti.
Excellently explained. So good.
Thank you very much!
Amazing Mate....you solved my problem in few mins.... Thanks a bunch!
Awesome!! You're welcome 👍
THANK YOU!! This is exactly what I was looking for. Not sure how I found it, but cheers to you sir.
Wonderful! You're very welcome.
Beautiful accent man! And great video, very insightful and well explained.
Thank you kindly!
Thanks mate. was looking for this.
No problem 👍
Thank you this is really helpful. Your way of explaining and teaching makes it easy!
Thank you very much Mira.
"Excel-lent", thank you
You're welcome Sidney 😅 Thank you.
Microsoft should develop simpler Excel functions for the users' benefit. Remove the guesswork.
You are a genius.
😊
HI Sir, i am from Bangladesh, very useful video, thanks
You're very welcome. Thank you.
It's really very helpful for use thank you very much
You're very welcome.
This was helpful, thank you
Great! No problem.
This video literally saved me!! Thank you!
You're welcome! Great to hear.
You are so spot on and your videos are great.
Thank you Bret.
brilliant. loved the cb2 / cb25 example.
Thank you.
really nice trick with the spacing!!
Thank you, Mo.
Thank you for uploading such elaborating Video on Excel word comparison. Helped me alot..
You're welcome, Keerthana. You're welcome.
Wow! that was very clear. I learned something new today. I should have stumbled on this earlier. Thanks!
Thank you 👍
Would there be a formula to identify the postcode and copy it to the next column for each address? ie] cell 2B would have CB5, cell 3B would have CB3, etc?
Very much useful.. thanks to you and your team..
You're very welcome Velavan. My pleasure.
awesome video - has saved me hours and makes me look far more intelligent than I really am :)
Excellent! Thank you 👍
Beautifully explained!
Thank you 😊
After 40 mins of time waste, finally, I find something useful. Thank you!!!!
You're welcome. Thank you.
Very good. Thanks!
My pleasure. Thank you.
thanks nice... very helpful....if i want to search many words then?
Thank you, Waqas. The OR function could be included to test multiple criteria/words. Or if it is a lot, such as >3 words, then a lookup table could be set up with the words, and a wildcard match using XLOOKUP or INDEX-MATCH used.
Thank you, this helped me for a work task. Appreciate it, liked and subscribed!!
Glad it helped! Thank you.
Thank you so much to ease my master journey! May God bless you
My pleasure Zack. Thank you.
Always more value than promise. Thanks for you kind help.
My pleasure Pankaj. Thank you.
Brilliant!!! Thank you so much! This helped me a great lot for my bookkeeping. Would love to know if we can set multiple conditions to expand on this: eg: if the adjacent cell contains "x" then the result is "1" and if the adjacent cell contains "y" then the result is "2" and so on.
Yes absolutely. One method is to create a lookup table with the a list as you have entered in this comment. And then use a lookup function wildcards to return the appropriate result. Here is a video that shows wildcard characters with VLOOKUP for a partial match th-cam.com/video/GJeSfXySByk/w-d-xo.html
Thanks for the video it solved a great problem of mine
Great to hear, Glisten. You're welcome.
Thank you so much for this video, straight to the point and explained so easily!
Thank you!
I want to know how to search the data in a different range of columns with if function? For example if A1 = any one cell in the range from d1:d5 then if true “Yes” if false “No”. Is this possible?
Thanks for the help!
You're welcome, James.
I love your logical analysis..!
Thank you 😊
thanks bro, greetings from istanbul
You're very welcome.
Love the videos. I was hoping you could make a video or point me to an existing one. I have a list of 50 employees with employee number name etc then multiple items that are required etc. If all 5 requirements say yes can I then highlight the row? currently the individual cells turn green but I'd love the whole row or their name to turn green if they get a yes in all the required training cells. I hope this makes sense trying to make it as short as possible.
Thank You very much !
You are welcome!
Thank you for this video. It helped me with my work. More videos please. Keep it up. 👍🏼
You're welcome, Noel. Happy to hear that I was able to help.
U saved my life love u
I love you too. No worries.
Fantastic tutorial! Thank you sir!
You're very welcome! Thank you.
Thank you so much for the video. This definitely helps! Another tool for the Excel toolbelt :)
You're welcome. That is great.
Thank you!!! This was very helpful and well explained. I appreciate it.
You're very welcome, Richard.
Great Video Thank you so much Guruji 🙏🙏🙏😎👍
Thank you very much.
Thank you so much, I helped me a lot🙏🙏
Great! Happy to hear that 👍
THANK YOU!!
You're welcome!
How would you write a formula to look for specific text (lets say in this case "Hello"), take a numbers in another row that is with "Hello" and get an average of all of the "Hello" numbers together.
By another row, do you mean column. For example, look for hello in A and return number from B, C and D and average them?
@@Computergaga So "Hello" would be in column A. Then all of "Hello" results would be in column B. I would want all of "Hello" results from column B averaged.
@@Computergaga Also i'm trying to use this formula multiple times.. is there a way to layer them? Basically like going through an entire spreadsheet looking for different words and putting a value for that word. I've been trying to add to the formula so I can do it all at once, but i'm having difficulty layering it into one cell.
This is Magic ,, Thank you
Thank you.
Wou, you just saved my day :) I would love if I could just copy paste formula from video description to the Excel. Haha, superlazy :D Subscribing for sure! Good luck.
Thank you, Rok 👍
Very good formula
Thank you, Sanjay.
What is i want
A1 is Paul Levine
B1 is Paul Levine Andrew
C1 is the formula to use to show TRUE as both are Paul Levine but additional text does not matter
How do i do this?
Use the SEARCH and ISNUMBER functions from the video for cell A1 and another for B1. Wrsp them in an AND function to ensure Paul Vevine is in both.
=AND(Search1,Search2)
@@Computergaga Can you pretty please do the full formula as i am struggling
@@Computergaga I tried writing the formula and editing it but no joy PLEASE HELP
Excellent!!!
Thank you 😊
Thank you Sir.
You're welcome
Cheers mate this helps
Thank You so much Sir
No problem 👍
Wow.....great sir....I use flash fill but flash fill doesn't always help....this one is very helpful. Thank you so much for the video
You're very welcome Anand. Yes Flash Fill is incredible.
tHANK YOU!
You're welcome, Jerwin.
Thanks a lot😊
Most welcome 😊
Really useful 😍
Thank you.
Great video ,👌
Thank you 👍
Thaaanks!
You're welcome, Plamen. Thank you.
Great video.
Thank you.
Thank you so much for your video! The cell contains many words/values and I would like to perform a function, if contains *certainword*, then copy that word/value into another cell. Is this possible?
Really useful tool. Thank you a lot!
You're welcome Uriel.
Brilliant video - thank you!
You're welcome. Thank you Anishca.
Wow! Alan, this is a great tip, many thanks for sharing a always.
Best regards
Mohideen
You're very welcome Mohideen.
Thank you for sharing your knowledge..
Can we use the "or" for multiple criteria or more than 2?
My pleasure, Donald. Yes absolutely. OR could be used to cater for multiple matches.
Thank you! It added a ton value. Do you know if it is possible to have an if function that searches for a particular string only if it does not contain other particular strings?
For example, an if function that finds "CB2", only if it does not contain, in your case, "1RB" ?
We could probably do this with the AND function for multiple tests and wrap a NOT function around the "1RB".
Many thanks 🙏🏻
No problem 👍