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!
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
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
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 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 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
@@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.
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.
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.
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 !
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
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.
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.
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!
Holy crap!!! That was way more informative than searching through stackoverflow!
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!
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.
You have saved my life in excel so many times. 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 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.
This is my online lecturer of all time...🙏
Be blessed teacher.
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!
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!
Thank you for the kind feedback. I'm glad you like the style of the videos.
Leila you are my best excel teacher that i have ever met
Wow, thank you!
You saved my life.... THANK YOU!! I was looking for a formula like this for soooo long.
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
Every time I stuck why is it always your videos that is most useful? :)) thanks soo much.
This is something I've been trying to work out for some time now. Thank you!!!
Thank you so much for this!
I thought I was going crazy, and didn't understand wild cards in Excel. Thank you for the help.👍
Thankyou, I was looking for this formula.
Fantastic - elegant solution.
Thank you! Cheers!
Every time I learn something new on this channel.
Great work Leila 👍
Been looking for something like this. This is awesome. Thank you!
You're very welcome!
Thank you so much for your tutorials Leila❤
This is Awesome! Thank you Leila!
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!
I watch your video because you are very nice☺ keep going
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.
Thank you Leila for these videos. Your explanations are so clear. 😊
I never tought to use the isnumber function. Awesome Leila. Thank you.
You're very welcome. Glad you like it!
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.
You have some of my favourite content in the world. Thank you
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.
Never used wildcards before in a formula. Nice video again. Thank you
Can be quite helpful sometimes. Glad you like it Rudolf.
Thanks Leila from Iraq
I used countif instead of using isnumber and search. Gave same results.
Thanks for the trick!
Our pleasure!
Really nice video. Quite helpful.
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. :)
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!
Awesome Ma'am, Thank you so much.
Saved me hours once again - thanks heaps!
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 :)
This was an incredibly helpful video. Thank you!
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.
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.
Always you save my projects, Leila ❤
😊
Thanks Leila. Another great one.
Another excellent video, Leila! Thank you!
You are a life saver Leila
I'm really glad to hear that :)
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.
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!
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
It was simple and exactly what I was looking for, even the control + enter was handy!
You just saved my day. Thank you so so much!!!!
Thanks, it helped me a lot!
Perfection, thank you!
Thank you, thank you, thank you!
Wonderful tips.
Glad you like it Muhammad.
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
Thank you. You made my task really easy.
Great!
Amazing Video and also replies from viewers
Nice mam good teaching god bless you...
I'm glad you like it.
Thank you for sharing.
this video was so useful and already I started to use it in my report.
Very nice. Thank you!
Liked this lesson
I'm glad to hear this.
You are an absolute magician!!!!!
Excel witchcraft :)
This is very useful to me although need some thinking to understand
Super Highly help ful
Thanks
I'm glad to hear that.
Fantastic, thank you for posting Leila..another time saver..
=IF(COUNTIF(A5;"*at*"); ... ; ... ) - BEST CHOICE FOR THIS TYPE OF TASKS!
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 :)
Very clever. Nice work Leila.
Glad you like it Will.
Great One!
insanely helpful
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 !
you are the best 👍
We are always impressed by your great solutions
I'm glad I can still impress you Réda :)
Nice explain
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
Oh my gosh this is EXACTLY what I was looking for!!! THANK YOU!! God Bless!!!
Glad it was helpful!
Really new for me
I was looking for this formula 😍 thanks alot Leila :)
You're welcome 😊
@@LeilaGharani I am facing an issue, if instead of Europe i want "AT" or "DE" to come how can i do that. Thank you in advance :)
Hi Leila can you please guide me on this too :)
Thank you this is very helfful and well explained
I was wondering about that for some time. Thanks for the informative video!
You're very welcome.
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
Thank you!
This is awesome, it helped with my current assignment
Nice technique, Leila. That will be very useful.
Glad you find it useful Mark!
A complex subject very well explained 😊
Really its great to know thanks for the video . Its very useful .........
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.
nice and simple
Glad you like it.