I had to check 7,000 entries of comments (long'ish text) from customers, we were interested in finding a particular word in the comments. Your tutorial here just saved me days of boring 'eye-balling' work, Wonderful! Thank you. BTW, I prefer your style of teaching than many others - it's always clear, no rambling and intuitive. Many thanks again.
I appreciate that you take us through the steps of things that won't work and explain why. This makes it easy to follow and is useful for cases where those functions that don't work in this case might be useful for something else we encounter in the future. Thank you for your work on this!
That awesome moment you come to see a Leila video because the title tells you this is going to be useful, and it is a formula that you use almost daily at your job. Feels amazing
Sincerely, the method of leila is better than your method...!! with your formula, you can not search for a number in a series of digits, unless the cell contains a combination of numbers and text
i was looking over 4 weeks for this formula in the internet and suddenly youtuve recomends me this video and you gave me the solution i finnaly needed thank you so much
I had to display list of values based on partial match of text in the previous column cells. This helped me resolve an issue which I was struggling for days. You are awesome. Thanks ! For anyone who is new in Excel, this channel is good info here.
I really get the excel formula thing here. Straight to the point not complicated and I understand it to express it in my own words. I have been to the the other guru sites as known - but here I have more satisfaction in learning, I should have come here first!
I find your explanations and applications to be the best. Even when I delve into some obscure situations you have answers that work. Thank you so much for your postings.
When I saw this video few months ago, I didn't remember much of it. Until I had to do something similar in DAX. Now when I check your video out again, I just realized whatever I watched had entered my subconscious! Yay. Keep posting these great contents, Leila.
Wow......I am very thankful to you Leila........I have applied the same to my worksheet and it really works........your tutorials save lot of man hours........thanks once again.........
I have been looking for something like this for probably over an hour, and you managed to solve my problem within the first 11 seconds of this video. I literally cannot thank you enough TuT
Hey Leila. I've used the isnumber function for this too but I think you can also do this with the If function along with a match since the match allows for the asterisks wildcards. Then if the match works you can use the if conditionals.
I freaking love you!! I consider myself excel literate but sometimes I struggle trying to make an argument work. So its like 'Hey, I wonder if Leila knows.' BAM! There it is. I am subscribing to everything you do. :)
Hi Leila.. cool solution. First I used IF with SEARCH and then wrapped the IF with IFERROR which trapped for #VALUE! when no match was found by SEARCH. Your method using ISNUMBER is more logical and I prefer it. Thanks for the insights on how to do partial match with IF using ISNUMBER, SEARCH and OR.. great stuff! Thumbs up!! PS - I also like the COUNTIF solution below by Alpha State and others.. always nice to see multiple ways to solve the problem.
Beautifully explained Long since I was wondering why the * function doesnt work to retrieve a particular letter while using IF Awesome piece of information
Mam I had solved many Excel problems with your Excel tutoring, I had started to learn Excel formula with your Excel tutoring , Thank You for that Now I had a problem that I can not use the index and match function Merged cell please give me a solution to this Mam at first I was entertaining to the company I don't know anything about Excel Formulas, I had started learning Excel Formulas with your Excel tutoring and then I was shining in my company Thank You for that Mam I am expecting you may give me a very good solution to this problem Thank you Mam
Last day someone ask me something like this, I gave her a way almost same as your solution but a little easier for two criteria. It will be smaller formula. =IF(OR(ISNUMBER(SEARCH({"DE","AT"},A5))),"Europe","") Thanks for your training. Mahmoud Baniasadi , from Iran
How do I carry over the exceeding value or the remaining one? For example I have to pay $10,000 but my salary is only $2,100 January is 0 all used and going vacation on february. February is 0 because of vacation and will only receive after I come back (weird company policy) March is 2,100 + 2,100 (paid leave from previous month) + 2,600 (Paid ticket) - 500 (expenses) = $6,300 April 2,100 so on and so forth so 5% of 2100 is 100 for savings. 25% of 2,000 is 500 for daily expenses. 1,500 is can Pay for remaning loan. if 10,000 - 6,300 = 3,700. How can I carry this remaining value to other cell. So that the next month which is 1,500 salary will be deducted.??? Hope you get my explaination.
@@LeilaGharani Other you tube tutorials was difficult to understand or sounds was not clear to hear. Your lesson is easy to understand, very comfortable and very practical. I subscribed you and start watching every video. Really appreciate your effort and reply.
Great video, what about if you had different kinds of fruits listed and want to know which fruit had a quantity sold is less than half of its initial quantity
AH :D that last part is what i really needed. I kept getting the N/A error until i watched the video. Still can't figure out why, but i also tried with IFS and got N/A, so thanks for the lesson !
and if you are doing the same basic thing but within VB, you can use InStr (and InStrRev), which also returns the location in the string if a match is found.
Hi, how can I use this same formula for multiple returns, such as you mentioned AT or DE then "Europe", but in my case I need to get, if AT then "Europe" or DE then "USA"
You are great at making complicated things simple. Thanks for this video . It is exactly what I'm looking for except I need it in Google Sheets. What tweaks should I make for it to work in Google Sheets?
Leila, thank you so much for all your tutorials. You explain it better than anyone else. Quick question-in your last example-on min 6:12, the formula that you have on top works for first part, but what if the statement is not true and I want to use again an "IF", so the logic can look for another "or" match. It does not seem to work for me. Thank you =IF(OR(ISNUMBER(SEARCH(122.5,C4)),ISNUMBER(SEARCH(150,C4))),"SMALL",IF(OR(ISNUMBER(SEARCH(150,C4)),ISNUMBER(SEARCH(224,C4)))),"MEDIUM",IF(ISNUMBER(SEARCH(315,C4))),"LARGE","")
I also like using this with a named range of values as an array formula as well when I want to search job titles within my dataset. For example, I would create a range list called "titles" and in it I would have things like CSR, Call Center, Customer Service, etc....so my formula would be {=if(isnumber(search([titles],[cell to search within])),1,0)} I put the 1 and 0 so I can quickly filter to just the "1's" and see all of those titles to ensure they were mapped to a specific division correctly.
I have an excel they I’ve done trims too; With personal information of people. I noticed at the end that some of the phone numbers don’t match up (most likely an error when I copied them over from the raw/original excels). I opened a new excel with the edited version and original side by side. Basically I would like to ask what the most efficient way to copy the accompanying phone numbers from the raw data to any exact match names in the edited version so that it mass swaps the numbers from raw to new for only names with exact matches. Thank you.
Grab the file I used in the video from here 👉 pages.xelplus.com/if-wildcard-file
I had to check 7,000 entries of comments (long'ish text) from customers, we were interested in finding a particular word in the comments. Your tutorial here just saved me days of boring 'eye-balling' work, Wonderful! Thank you. BTW, I prefer your style of teaching than many others - it's always clear, no rambling and intuitive. Many thanks again.
I appreciate that you take us through the steps of things that won't work and explain why. This makes it easy to follow and is useful for cases where those functions that don't work in this case might be useful for something else we encounter in the future. Thank you for your work on this!
You're very welcome Brian. I'm really glad if I can provide some useful alternatives.
@@LeilaGharani THANKS! But there is other variant how to say "contains" : =IF(COUNTIF(A5;"*at*"); ... ; ... ) - BEST CHOICE FOR THIS TYPE OF TASKS!
That awesome moment you come to see a Leila video because the title tells you this is going to be useful, and it is a formula that you use almost daily at your job. Feels amazing
Happy to hear that! Thank you for your kind comment Meli.
This does the job as well:
=IF(COUNTIF(A5,"*AT*")>0,"AT","")
This one is amazing, because it's works also with "?" wildcard.
Thank you for this! :)
This is Good one 👍
I LIKE this! Thank you for sharing : )
Nice. It works like this as well: =IF(COUNTIF(A5,"*AT*"),"AT","")
Sincerely, the method of leila is better than your method...!! with your formula, you can not search for a number in a series of digits, unless the cell contains a combination of numbers and text
i was looking over 4 weeks for this formula in the internet and suddenly youtuve recomends me this video and you gave me the solution i finnaly needed thank you so much
Great, I'm glad I was able to help with that Sven!
Holy crap!!! That was way more informative than searching through stackoverflow!
You have saved my life in excel so many times. Thank you
This is my online lecturer of all time...🙏
Be blessed teacher.
I had to display list of values based on partial match of text in the previous column cells. This helped me resolve an issue which I was struggling for days. You are awesome. Thanks ! For anyone who is new in Excel, this channel is good info here.
WOW! knowing this channel is like seeing water in the mids of the hot desert. Thank you, Laila. Much love!
Thank you very much for the kind words Abubakar! I'm glad you like the videos.
I really get the excel formula thing here. Straight to the point not complicated and I understand it to express it in my own words. I have been to the the other guru sites as known - but here I have more satisfaction in learning, I should have come here first!
Thank you for the kind feedback. I'm glad you like the style of the videos.
I find your explanations and applications to be the best. Even when I delve into some obscure situations you have answers that work. Thank you so much for your postings.
When I saw this video few months ago, I didn't remember much of it. Until I had to do something similar in DAX.
Now when I check your video out again, I just realized whatever I watched had entered my subconscious! Yay. Keep posting these great contents, Leila.
Oh wow, I didn't know my videos had such an effect :) Glad you find the tutorials helpful Alex.
@@LeilaGharani ma'am I'm from India and you
Wow......I am very thankful to you Leila........I have applied the same to my worksheet and it really works........your tutorials save lot of man hours........thanks once again.........
Hey Leila have been following you regularly, your online classes made me look like a king before my colleagues at office, thanks a lot!
Leila you are my best excel teacher that i have ever met
Wow, thank you!
Thanks Leila😊
Once again, you show a learning video that's easy to understand for everyone👍
You're very welcome Peter! Glad it comes across like that.
I have been looking for something like this for probably over an hour, and you managed to solve my problem within the first 11 seconds of this video. I literally cannot thank you enough TuT
Glad I could help
Thank you... I searched everywhere but no solution... You're a life saver.
Glad to help :)
You saved my life.... THANK YOU!! I was looking for a formula like this for soooo long.
Hey Leila. I've used the isnumber function for this too but I think you can also do this with the If function along with a match since the match allows for the asterisks wildcards. Then if the match works you can use the if conditionals.
This is something I've been trying to work out for some time now. Thank you!!!
I thought I was going crazy, and didn't understand wild cards in Excel. Thank you for the help.👍
Every time I stuck why is it always your videos that is most useful? :)) thanks soo much.
This was exactly what I was looking for and I was able to write my formula with IF + wildcards and it solved my problem. Thank you!
You have some of my favourite content in the world. Thank you
Every time I learn something new on this channel.
Great work Leila 👍
I used search with wildcards and its results are magic... Good to see the same in your video...
Agreed, it can really be helpful sometimes.
@@LeilaGharani i used it creating reminders, will share with you once its done.
Great! Looking forward to it.
Thank you Leila for these videos. Your explanations are so clear. 😊
I used countif instead of using isnumber and search. Gave same results.
Gr8
You are a SOLUTION to any issue in Excel.
Tons of Permutation n c Combination can be worked with your this invention.
Thnx
Thanks for the kind words Shabbir. Glad you like it!
You do a phenomenal job at explaining how to use Excel Leila. Please keep up the good work!
Thank you for the kind feedback. I'm glad the tutorials are helpful.
Is there a way to use {…,…} in place of typing out the search function twice? The only thing that is really different is the criteria.
I freaking love you!! I consider myself excel literate but sometimes I struggle trying to make an argument work. So its like 'Hey, I wonder if Leila knows.' BAM! There it is. I am subscribing to everything you do. :)
I never tought to use the isnumber function. Awesome Leila. Thank you.
You're very welcome. Glad you like it!
Fantastic - elegant solution.
Thank you! Cheers!
Never used wildcards before in a formula. Nice video again. Thank you
Can be quite helpful sometimes. Glad you like it Rudolf.
Thank you so much for this!
I watch your video because you are very nice☺ keep going
I thoroughly enjoy watching your videos. Very informative and practical. This is stuff you can actually use in real-world applications. Thanks!
Really glad to hear that Mike. Thanks for the feedback.
It was simple and exactly what I was looking for, even the control + enter was handy!
Thanks Leila from Iraq
Very helpful, but so too was that little reminder about filling without formatting at 4:44 - I hadn't realised you could do that. So thanks twice!
You're very welcome Terry. I'm glad it was useful twice :)
Thankyou, I was looking for this formula.
Hi Leila.. cool solution. First I used IF with SEARCH and then wrapped the IF with IFERROR which trapped for #VALUE! when no match was found by SEARCH. Your method using ISNUMBER is more logical and I prefer it. Thanks for the insights on how to do partial match with IF using ISNUMBER, SEARCH and OR.. great stuff! Thumbs up!!
PS - I also like the COUNTIF solution below by Alpha State and others.. always nice to see multiple ways to solve the problem.
Exactly, that's why I really appreciate comments :) Thanks for the thumbs up!
This is Awesome! Thank you Leila!
Saved me hours once again - thanks heaps!
Awesome video and a good tip of the IF function. Thanks Leila we appreciate you so much. please, keep up with the great work.
You're very welcome. It can come in quite handy sometimes.
Been looking for something like this. This is awesome. Thank you!
You're very welcome!
Thank you so much for your tutorials Leila❤
this video was so useful and already I started to use it in my report.
This was an incredibly helpful video. Thank you!
Awesome Ma'am, Thank you so much.
Beautifully explained Long since I was wondering why the * function doesnt work to retrieve a particular letter while using IF Awesome piece of information
Mam I had solved many Excel problems with your Excel tutoring, I had started to learn Excel formula with your Excel tutoring , Thank You for that
Now I had a problem that I can not use the index and match function Merged cell please give me a solution to this
Mam at first I was entertaining to the company I don't know anything about Excel Formulas, I had started learning Excel Formulas with your Excel tutoring and then I was shining in my company Thank You for that
Mam I am expecting you may give me a very good solution to this problem
Thank you Mam
Thanks Leila. Another great one.
You are a life saver Leila
I'm really glad to hear that :)
Last day someone ask me something like this, I gave her a way almost same as your solution but a little easier for two criteria.
It will be smaller formula.
=IF(OR(ISNUMBER(SEARCH({"DE","AT"},A5))),"Europe","")
Thanks for your training.
Mahmoud Baniasadi , from Iran
How do I carry over the exceeding value or the remaining one?
For example
I have to pay $10,000 but my salary is only $2,100
January is 0 all used and going vacation on february.
February is 0 because of vacation and will only receive after I come back (weird company policy)
March is 2,100 + 2,100 (paid leave from previous month) + 2,600 (Paid ticket) - 500 (expenses) = $6,300
April 2,100 so on and so forth
so 5% of 2100 is 100 for savings.
25% of 2,000 is 500 for daily expenses.
1,500 is can Pay for remaning loan.
if 10,000 - 6,300 = 3,700. How can I carry this remaining value to other cell. So that the next month which is 1,500 salary will be deducted.???
Hope you get my explaination.
@@kurapikanostrad4592 put your input in an excel sheet and send it to me.
N.baniasadi67@gmail.com
This is really I was looking for. Thank you.
Glad I could help!
@@LeilaGharani Other you tube tutorials was difficult to understand or sounds was not clear to hear.
Your lesson is easy to understand, very comfortable and very practical.
I subscribed you and start watching every video.
Really appreciate your effort and reply.
Very useful.. But little bit difficult! love you Leila 😍
Great video, what about if you had different kinds of fruits listed and want to know which fruit had a quantity sold is less than half of its initial quantity
AH :D that last part is what i really needed. I kept getting the N/A error until i watched the video. Still can't figure out why, but i also tried with IFS and got N/A, so thanks for the lesson !
Thank you, thank you, thank you!
Thanks for the trick!
Our pleasure!
Another excellent video, Leila! Thank you!
You just saved my day. Thank you so so much!!!!
Really nice video. Quite helpful.
very useful,
dont know, who r those losers disliking this informative video
Thank you. You made my task really easy.
Great!
Perfection, thank you!
and if you are doing the same basic thing but within VB, you can use InStr (and InStrRev), which also returns the location in the string if a match is found.
This is very useful to me although need some thinking to understand
Fantastic, thank you for posting Leila..another time saver..
Thanks, it helped me a lot!
Leila - Thank you for all the Excel videos. They are all so helpful and I have enjoyed every one so far. Please don't stop.
I'm glad to hear that and don't worry Dustin. I'll keep them coming :)
Always you save my projects, Leila ❤
😊
Thank you for sharing.
Amazing Video and also replies from viewers
Thanks Leila👍👌👏. Love you from Iran 😊
Oh my gosh this is EXACTLY what I was looking for!!! THANK YOU!! God Bless!!!
Glad it was helpful!
Very smart. Thanks Leila
You're very welcome. Glad you like it.
You are an absolute magician!!!!!
Excel witchcraft :)
Very nice. Thank you!
Hi, how can I use this same formula for multiple returns, such as you mentioned AT or DE then "Europe", but in my case I need to get, if AT then "Europe" or DE then "USA"
=IF(ISNUMBER(SEARCH($C$4,A8)),"Europe",IF(ISNUMBER(SEARCH($C$3,A8)),"USA",""))
Thank you Leila. I have been stuck for more than a month now trying to configure how to handle this...can i get the formula version for power query?
Wonderful tips.
Glad you like it Muhammad.
Thank you this is very helfful and well explained
Nice mam good teaching god bless you...
I'm glad you like it.
insanely helpful
You are great at making complicated things simple. Thanks for this video . It is exactly what I'm
looking for except I need it in Google Sheets. What tweaks should I make for it to work in Google Sheets?
I considered subscribing to this channel
I hope you do. We'd be happy to have you here.
Thanks Leila, You have excellence in Excel. Make a video on Pivot Table and how to make as good as report from it.
You're very welcome. I cover Pivot Tables in my Advanced Course. I will add more videos on UA-cam too. Thank you for your suggestion.
Leila, thank you so much for all your tutorials. You explain it better than anyone else. Quick question-in your last example-on min 6:12, the formula that you have on top works for first part, but what if the statement is not true and I want to use again an "IF", so the logic can look for another "or" match. It does not seem to work for me. Thank you
=IF(OR(ISNUMBER(SEARCH(122.5,C4)),ISNUMBER(SEARCH(150,C4))),"SMALL",IF(OR(ISNUMBER(SEARCH(150,C4)),ISNUMBER(SEARCH(224,C4)))),"MEDIUM",IF(ISNUMBER(SEARCH(315,C4))),"LARGE","")
Thank you, Leila 🙏
What if is search for a date instead "AT"?
I also like using this with a named range of values as an array formula as well when I want to search job titles within my dataset. For example, I would create a range list called "titles" and in it I would have things like CSR, Call Center, Customer Service, etc....so my formula would be {=if(isnumber(search([titles],[cell to search within])),1,0)} I put the 1 and 0 so I can quickly filter to just the "1's" and see all of those titles to ensure they were mapped to a specific division correctly.
That's a good use case! Thank you for sharing Nigel.
It's a great function
This is awesome, it helped with my current assignment
I have an excel they I’ve done trims too; With personal information of people. I noticed at the end that some of the phone numbers don’t match up (most likely an error when I copied them over from the raw/original excels). I opened a new excel with the edited version and original side by side.
Basically I would like to ask what the most efficient way to copy the accompanying phone numbers from the raw data to any exact match names in the edited version so that it mass swaps the numbers from raw to new for only names with exact matches.
Thank you.
A complex subject very well explained 😊