@@txreal2 No problem 😁 But this time it will only apply for the "Insider preview" version of Excel 365 (because of REGEXEXTRACT function). First, simple case (like in Mike's video). =XLOOKUP(--REGEXEXTRACT(E3:E26,"\d{6}",0),$C$3:$C$14,$B$3:$B$14) and this applies to slightly more complicated cases like "654321/555555/7201002546954WA sreet PND-Exempt Wages" =XLOOKUP(--REGEXEXTRACT(E3:E26,"\/(\d{6})\d*\s*[A-Za-z]+",2),$C$3:$C$14,$B$3:$B$14)
Dear Mike, you know how many great actors never got the Oscar trophy? actually my favorite actor Tom Cruise never got one, but in my book and millions other, he is number one. Like the academy, the Microsoft awards committee, probably a bunch of kids looking for façade instead of essence, ignored your work this time; so what! we, your followers(over millions), have already Nominated you Sir, for MVP. This is the channel when millions come to learn and refresh excel knowledge every day, and will continue do so, because your videos are unmatched, your work is excellent. Scorpio!
I don't comment often but I am a fan of your work and creativity. When people want to learn excel, I point them in your direction. forever MVP in my book.
Mike, just letting you know your book on Excel CRTL Shift Enter arrays, pre dynamic arrays is my favourite book of all time bar none. That book alone should have guaranteed your MVP status for life!
Brilliant and infectious enthusiasm as always- heard you had an accident- hope your feeling better. And 🖕to Microsoft for not renewing your MVP, your DAME course just recently was awesome.
Microsoft must not have seen the DAME class: there are zero other full free classes on Microsoft Power Tools that are better. I guess the criteria for MVP is not that stuff is good.
Thanks Mike!!! I hope that Microsoft will realise the mistake and acknowledge you as the best MVP who have reached so many people with your talent in teaching!! :)
OK. I never show my face, and I never will with longer videos because there are just too many edits. But, please do tell me, are the short videos better when I show my face?
The best channel in the world for data analytics and MS products ,am a data analyst working in a renown MNC...i always prefer your channel.. whenever its required
Good to see you back Michael. Great example: I thought for a moment that you will do a flash fill trick, but that mid trick was even better. Take care!
Thank you Mike!!! You are MVP forever. @Microsoft should take a closer look at all your achievements - I often go back to your previous videos if I want to understand an Excel problem, not just take a ready solution. Please know you shaped Excel community on youtube, you are Excel guru and fantastic teacher! Thank you for all the joy you brought to my work life!❤
So, do please tell me: do you like the videos of me talking? I can not do it for my longer videos, because there are just too many edits. But for shorter videos, is it better if I post the videos of me too?
@@excelisfun Absolutely! It’s wonderful that you’re considering sharing more videos of yourself. 😊.People often connect more with genuine, unfiltered moments. Short videos where you’re just being yourself can resonate with viewers.
I can not have a camera on me for longer videos because there are just too many edits. But for the shorter videos, does the video of me make the video better?
Great Trick Mike, I didn't know the control Enter did that and i would have used LEFT and the Double Unary -- to convert text to numbers but it's a very great video.
Thanks Mike, my try for the old school: =VLOOKUP(--MID(E3,5,6),IF({1,0},VC[Code],VC[Value]),2,0) for the new school: =XLOOKUP(MID(E3:E26,5,6),""&VC[Code],VC[Value]) In the second formula since the number of data in E3:E26 is more than VC[Code] column I didn't use -- and joined an empty string to VC[Code] to convert it to text.
Thank you Mike. That math operator trick always amaze me because I never remember it. I always wrap extracted text in the VALUE function. Yours is much easier, I must practice using it. You're the best, keep going Mike, it is the impact that really count. Acts 20:35
One of your easier formulas but still always fun :) XLOOKUP is such a game changer compared to V or H lookup. Or the even more old-school way of INDEX/MATCH. Thanks for sharing "real world" problems/solutions Mike.
Riiiiight. This sort of formula is extremely hard for 99% of Excel users. But for us, hard core Excel Formula Creators, it is easy. When the student came into my office today, he was so engaged and interested, but I had to explain many concepts that went into this formula. For Excel pros it is all assumed. But for the new, it is all new and wonderful!!!! I agree with you soooooo much about XLOOKUP. It is THE lookup formula now : ) Lucky us.
@@excelisfun I do a ton of Excel training with my coworkers so I 100% understand what you're talking about. Explaining simple things like Pivot Tables or adding calculated fields to new users can be eye-opening for new users. Your channel with the excellent playlists for all levels is the first thing I point people to. Thanks for all you do!
@@tecwzrd You are welcome!!!! I am happy to be a fellow teacher with you and many others. It is so wonderful when we can help and see someone else get it and learn: the happy light bulb : ) : ) : )
Hello Sir, this is the first time I have across your channel. I want to learn power query m language but there are so many videos regarding m language I am slightly overwhelmed. Could you help me which video should I start watching for m language learning. Thank you.
If you want to learn M, I have one 2 hour video. But it is very dense and has everything. If you want toi learn Power Query and M, meaning you want to lean the basics of Power Query too, then this video would not be good. M Code Video: ruclips.net/video/HRFZOYWGhjo/видео.html for Powe Query and M Code: ruclips.net/video/FLzKnNmE4Ms/видео.html
Microsoft going nuts here.if anyting iearnt everything from your channel.id this is not MVP then god knows hat criteria Microsoft is using..Once again Thanks and Continue with your Pasion,ignoring the nuts at microsoft
Hi Lovely mike ❤ I am studying CS in 3rd semester. I am beginner. I want data analysis course which of your playlist is best for me. I am currently watching Complete 365 MECS Can you please recommend me. I shall be very thankful to you.❤
Complete 365 MECS is a great class with everything about both Excel and Power BI for both 1) models and calculations, and 2) data analysis. If you want the class for just data analysis: ruclips.net/p/PLrRPvpgDmw0lAIQ6DPvSe_hfAraNhTvS4
@@excelisfun Thanks for your kind time and reply. Can I learn Data analysis completely means I became Professional with 365 MECS or we have to watch the shared playlist after watching 365 MECS ?
@@researchwork_1 I do not teach complete data analysis. I teach complete data analysis with MS Power Tools. Yes, either playlist can teach complete. But it will make months of study to absorb and apply creatively.
If it moves around but still follows some sort of pattern (e.g., it's not always the 5th character but it IS always immediately following a "/" ) then you can program it. You would just need to use something like FIND (I think) to locate the place in the text where the / is and then go one from there.
It always comes down to a pattern in the data. Just as I mentioned in the video. If there is no patter, you can not build a solutuion that works 100% of the time. Recognizing patterns takes practice. You always use logic. When the lookup value changes position, then you try to find delimiters that define where the lookup value is located. A delimiter is a character that always proceeds and comes after the lookup value. For example, this formula would work: =XLOOKUP(TEXTBEFORE(TEXTAFTER(B21:B30,"/"),"992")+0,VC[Code],VC[Value]) if the delimiter "/" always proceeds the lookup value and the delimiter "992" always comes after the lookup value. How to tackle is ALWAYS: can you recognize a consistent patter. 100% of the time.
Hi Given Table 8*8, a table containing data of company sales across products country wise. Since it is sales data of 8 products of the company for 8 countries. I have been given three products in sheet 2 say product A ( cell reference B1), product B (C1) and product C (D1). Cell A1 will contain the name of the country which I have to fill using formula. Problem 1: I have to use a formula which will only fill the list of countries (starting from A2 cell to A8) which has a sale of product A or B or C only. Problem 2: Say I have now only four countries from cell A2:A5. Now I have to get their sales in araay B2:D5 (for product A,B and C. I can get it using the lookup but I want to get it by only writing a dynamic array formula in B2 cell.
I have tried to understand what you are doing. But I have NO IDEA how to solve this because I can not understand what you are writing. I am sorry. There are people that are much smarter than me at interpreting what people ask. You can try the mrexcel.com/board web site to ask people that are much smarter than me.
Hi Mike, nice trick with "+0"/"x1", but what other reason than less typing you have that you dont use VALUE()? =XLOOKUP(VALUE(MID(E3,5,6),$C$3:$C$14,$B$3:$B$14)
@txreal2 I can't reply to your comment because YT rejects my replies (I don't know why). So I'm trying to post this completely separate comment. Excel related tricks, no problem :-) But this time it will only apply for the "Insider preview" version of Excel 365 (because of REGEXEXTRACT function). First, a simple case (like in Mike's video). =XLOOKUP(--REGEXEXTRACT(E3:E26,"\d{6}",0),$C$3:$C$14,$B$3:$B$14) and this applies to slightly more complicated cases like "654321/555555/7201002546954WA sreet PND-Exempt Wages" =XLOOKUP(--REGEXEXTRACT(E3:E26,"\/(\d{6})\d*\s*[A-Za-z]+",2),$C$3:$C$14,$B$3:$B$14) 😁
Mike, you are and always will be the MVP of all MVPs.
Shame on you, Microsoft.
Thank you, O Great Poet of PQ!!!!!! : ) : )
Mr. Szysz. How are you?
Please share some great Excel related tricks :)
@@txreal2 We love Bill Szysz : ) : ) : ) : ) : ) : ) : ) : ) : ) : ) : ) : ) : ) : )
@@txreal2 No problem 😁 But this time it will only apply for the "Insider preview" version of Excel 365 (because of REGEXEXTRACT function). First, simple case (like in Mike's video).
=XLOOKUP(--REGEXEXTRACT(E3:E26,"\d{6}",0),$C$3:$C$14,$B$3:$B$14)
and this applies to slightly more complicated cases like "654321/555555/7201002546954WA sreet PND-Exempt Wages"
=XLOOKUP(--REGEXEXTRACT(E3:E26,"\/(\d{6})\d*\s*[A-Za-z]+",2),$C$3:$C$14,$B$3:$B$14)
@@excelisfun We all love Mike😁
Dear Mike, you know how many great actors never got the Oscar trophy? actually my favorite actor Tom Cruise never got one, but in my book and millions other, he is number one.
Like the academy, the Microsoft awards committee, probably a bunch of kids looking for façade instead of essence, ignored your work this time; so what! we, your followers(over millions), have already Nominated you Sir, for MVP. This is the channel when millions come to learn and refresh excel knowledge every day, and will continue do so, because your videos are unmatched, your work is excellent. Scorpio!
Thank you. long time Teammate!!!!
I don't comment often but I am a fan of your work and creativity. When people want to learn excel, I point them in your direction. forever MVP in my book.
Thank you, thank you, Im David V!!!!
I second that wholeheartedly!
Same here, recommended your channel to a colleague just last week
@@GeertDelmulle Thanks, Dear Teammate!!!!!
@@peekaboo6168 I am so happy to help!!!!!
you've shared your knowledge so generously for years, even though not MVP you're defiantly a VIP, thank you for your vids
You are welcome!!!!!
The best man ever to express the excel features
Thanks : ) : ) Go Team!!!!
Mike, just letting you know your book on Excel CRTL Shift Enter arrays, pre dynamic arrays is my favourite book of all time bar none. That book alone should have guaranteed your MVP status for life!
Thanks for that cool Ctrl Shift Enter comment, Brian!!!!
Brilliant and infectious enthusiasm as always- heard you had an accident- hope your feeling better. And 🖕to Microsoft for not renewing your MVP, your DAME course just recently was awesome.
Microsoft must not have seen the DAME class: there are zero other full free classes on Microsoft Power Tools that are better. I guess the criteria for MVP is not that stuff is good.
Thanks Mike!!! I hope that Microsoft will realise the mistake and acknowledge you as the best MVP who have reached so many people with your talent in teaching!! :)
Thanks, Formula Guy John!!!!!
And he’s back!!! Super video as always Mike, and having yourself on camera just makes your enthusiasm even better. Go Team!!!!
OK. I never show my face, and I never will with longer videos because there are just too many edits. But, please do tell me, are the short videos better when I show my face?
Thanks, Amazing Teammate Chris M!!!
I think so, yes!
@@excelisfunyes it is🙂👍
@@chrism9037 Thanks for the feedback : )
The best channel in the world for data analytics and MS products ,am a data analyst working in a renown MNC...i always prefer your channel.. whenever its required
For MS Power Tools, the excelisfun channel has your back!!!! : )
Slick! Long-time MVP, sir.
Thank you very much : ) : )
Thanks Mike for the continous sharing the magic tricks... Our forever MVP.😊
You are welcome!!!!
Good to see you back Michael. Great example: I thought for a moment that you will do a flash fill trick, but that mid trick was even better. Take care!
Thank you, long time Teammate Jazzista!!! : ) : ) : )
Another top example to add to the long list of helper info from you along with your ebook. Your my MVP when it comes to excel.
Thank you so much, David!!!!
Thank you Mike!!! You are MVP forever. @Microsoft should take a closer look at all your achievements - I often go back to your previous videos if I want to understand an Excel problem, not just take a ready solution. Please know you shaped Excel community on youtube, you are Excel guru and fantastic teacher! Thank you for all the joy you brought to my work life!❤
Thank you very much!!!! I will keep happily posting for you and our Team!!!!!
I like watching you teaching in the corner of the screen. The video is awesome as always. Thank you Mike :)
Glad you enjoy it! I will keep doing it for short videos!!!!
Mike has shown this in other videos, but if you want to go the text-only route, you can add &"" to each of the arrays to make both arrays text.
Cool : ) I have definitely done that in other videos.
Thank you Mike for this great video. I always enjoy watching your videos and love going back to 2008 onwards. U have been doing a stellar job. 🎉
I have been having fun hanging out with you, Nader, for so many years!!!!!
very simple but effective, good tip and all the math made in one column.
: ) : ) : )
You are the best Excel trainer that I have come across. My all-time MVP.
I shall be looking forward for your videos.
❤❤❤
: ) : ) : )
Very nice tip on that +0 that one did could have saved me a few min. of irritation in the past.
Me too - before I learned it : )
This new presentation is awesome Mike! BAM! it has been almost 12 years following you.😊
So, do please tell me: do you like the videos of me talking? I can not do it for my longer videos, because there are just too many edits. But for shorter videos, is it better if I post the videos of me too?
@@excelisfun Absolutely! It’s wonderful that you’re considering sharing more videos of yourself. 😊.People often connect more with genuine, unfiltered moments. Short videos where you’re just being yourself can resonate with viewers.
Cool, I've never thought to add 0 to do the trick. I've always used *1 or -- in order to convert the result into a number.
: ) : )
the BAM 🔥 was needed at the end RAD Mike! thanks reminding us about MID function! you're looking better also myfriend 🙏!!
Boom! : ) : )
Gracias Mike, tu trabajo es increíble! Gracias por seguirlo haciendo. Salir en camara es una excelente decisión. Felicidades!
I can not have a camera on me for longer videos because there are just too many edits. But for the shorter videos, does the video of me make the video better?
@@excelisfun Si, absolutamente. La imagen del autor de las clases mejora mucho cada video.
Another great video Mike. Let's hope Satya Nadella is watching this as well!
I hope so too! ; )
Excel fans' MVP always
Keep making videos. Go Team!!!!!
Glad you're back Mike. Thanks for sharing your wealth of knowledge.
I will keep posting for our Team!!!!
Thank you, Mike for your resilience. Always love your videos.
You are welcome. Resilience and duty to try and make the world a better place and fun is all I know : )
Great Trick Mike, I didn't know the control Enter did that and i would have used LEFT and the Double Unary -- to convert text to numbers but it's a very great video.
Glad you like it!!!!!
Thanks. It's a great tip on how to go about extracting a number from a text having numbers.
Glad that this tip helps, Seeker!!!
Dear Mike,
How I appreciate spilled formulas:
=XLOOKUP(VALUE(MID(E3:E26,5,6)),C3:C14,B3:B14) 🤗
Very nice us of VALUE!!!
Thanks Mike, my try for the old school:
=VLOOKUP(--MID(E3,5,6),IF({1,0},VC[Code],VC[Value]),2,0)
for the new school:
=XLOOKUP(MID(E3:E26,5,6),""&VC[Code],VC[Value])
In the second formula since the number of data in E3:E26 is more than VC[Code] column I didn't use -- and joined an empty string to VC[Code] to convert it to text.
Love both!!!!!! IF({1,0) : ) : )
Thanks Mike! Looking great!
Even with my smashed up face lol Thanks, TopBam!!!
Thank you very much Mike, for sharing highly informative video as slways.
You are welcome!!!!
a great devotion to teaching, excellent great teacher
Always for the Team!!!!!
No longer an MVP? pffft. You taught me array formulas years back and I have been a fan ever since.
That is what Microsoft said, but it will never stop me from having fun with our Team!!!!!
Love the new screen format! Awesome as always. Thanks, Mike
So tell me, do you like it better if my image is in it? I can't use the image in longer videos, but is it preferable in shorter videos?
@@excelisfun Yes Mike! Good enough if it is only in shorter videos. Understandable!
@@elbadlis : ) : ) : ) : )
Very sad to hear about your Excel MVP status Mike.
In any case, your efforts in Excel education for people like myself will forever be appreciated.
I will keep posting for our Team!!!!
Sir that's a sick trick!! Mindblown❤❤❤
Glad you like it : ) : )
Thank you Mike.
That math operator trick always amaze me because I never remember it. I always wrap extracted text in the VALUE function. Yours is much easier, I must practice using it.
You're the best, keep going Mike, it is the impact that really count. Acts 20:35
Worksheet formulas and DAX formulas can do that math to convert text number to number, but not M Code.
Thank you, Mike MVP!
You are welcome, Teammate!!!!
Love your energy!
Thanks, Teammate!!!
Another wonderful video Mike, so many tricks in 3 min. Thank you so much 😊
You are welcome so very much, Katerina!!! : )
I think Microsoft is working on creating a title greater than MVP to befit your tremendous efforts
Thank You : ) : ) : )
Thanks Mike. You never ceaze to amaze!
Wow, thanks! : ) :)
One of your easier formulas but still always fun :) XLOOKUP is such a game changer compared to V or H lookup. Or the even more old-school way of INDEX/MATCH. Thanks for sharing "real world" problems/solutions Mike.
Riiiiight. This sort of formula is extremely hard for 99% of Excel users. But for us, hard core Excel Formula Creators, it is easy. When the student came into my office today, he was so engaged and interested, but I had to explain many concepts that went into this formula. For Excel pros it is all assumed. But for the new, it is all new and wonderful!!!! I agree with you soooooo much about XLOOKUP. It is THE lookup formula now : ) Lucky us.
@@excelisfun I do a ton of Excel training with my coworkers so I 100% understand what you're talking about. Explaining simple things like Pivot Tables or adding calculated fields to new users can be eye-opening for new users. Your channel with the excellent playlists for all levels is the first thing I point people to. Thanks for all you do!
@@tecwzrd You are welcome!!!! I am happy to be a fellow teacher with you and many others. It is so wonderful when we can help and see someone else get it and learn: the happy light bulb : ) : ) : )
Jordan couldn’t do it better 🐐
Thanks for all
You are welcome!!!!
love you mike Always and Forever❤
: ) : ) : ) : )
Cool. I would have thought of text to column. Then trim the result. Then xlookup.
Cool formula 😎
Glad that you like the cool formula!!!!
excellent master
: ) : ) : ) EXCELlent ; )
Great 👍🏻
Glad you like it!!!!
That's Amazing Mike ... Many Thanks
Many you are welcomes, Long Time Friend!!!!
That was awesome
Glad you liked it!!!
Amazing
Glad it is amazing for you!!!!
MVPF...MVP Forever!!
That is very kind of you : ) : ) : )
Please make a detailed video on List.Accumulate function
Here is one I already made:
ruclips.net/video/I0UnX0PeGs4/видео.html
I also have a whole book on M Code coming out in one month.
Great tricks as always, sensei
Great to be back and making videos for you and our Team!!!
Hello Sir, this is the first time I have across your channel. I want to learn power query m language but there are so many videos regarding m language I am slightly overwhelmed. Could you help me which video should I start watching for m language learning. Thank you.
If you want to learn M, I have one 2 hour video. But it is very dense and has everything. If you want toi learn Power Query and M, meaning you want to lean the basics of Power Query too, then this video would not be good.
M Code Video: ruclips.net/video/HRFZOYWGhjo/видео.html
for Powe Query and M Code: ruclips.net/video/FLzKnNmE4Ms/видео.html
@@excelisfun Thank you. I will start watching it soon.
Microsoft going nuts here.if anyting iearnt everything from your channel.id this is not MVP then god knows hat criteria Microsoft is using..Once again Thanks and Continue with your Pasion,ignoring the nuts at microsoft
You are welcome!!! We'll keep having fun no matter what MS does!!!!
Could you have also nested the MID inside a VALUE() function to get it to return as a number? As an alternative to doing the +0.
Absolutely!!!!!
Hello! Thanks for the tutorial.
I was wondering if there's any reason why you're doing +0 instead of NUMBERVALUE to convert it to number
Great video! By the way, do you have any video using wildcard ? I wonder if that can help to look up in this case
Great trick, Thanks
You are welcome!!!
Hi Lovely mike ❤
I am studying CS in 3rd semester.
I am beginner.
I want data analysis course which of your playlist is best for me. I am currently watching
Complete 365 MECS
Can you please recommend me.
I shall be very thankful to you.❤
Complete 365 MECS is a great class with everything about both Excel and Power BI for both 1) models and calculations, and 2) data analysis. If you want the class for just data analysis: ruclips.net/p/PLrRPvpgDmw0lAIQ6DPvSe_hfAraNhTvS4
@@excelisfun
Thanks for your kind time and reply.
Can I learn Data analysis completely means I became Professional with 365 MECS or we have to watch the shared playlist after watching 365 MECS ?
@@researchwork_1 I do not teach complete data analysis. I teach complete data analysis with MS Power Tools. Yes, either playlist can teach complete. But it will make months of study to absorb and apply creatively.
what if the required lookup value is not in the same position in every row. How can we tackle such situations??
If it moves around but still follows some sort of pattern (e.g., it's not always the 5th character but it IS always immediately following a "/" ) then you can program it. You would just need to use something like FIND (I think) to locate the place in the text where the / is and then go one from there.
It always comes down to a pattern in the data. Just as I mentioned in the video. If there is no patter, you can not build a solutuion that works 100% of the time. Recognizing patterns takes practice. You always use logic. When the lookup value changes position, then you try to find delimiters that define where the lookup value is located. A delimiter is a character that always proceeds and comes after the lookup value. For example, this formula would work:
=XLOOKUP(TEXTBEFORE(TEXTAFTER(B21:B30,"/"),"992")+0,VC[Code],VC[Value])
if the delimiter "/" always proceeds the lookup value and the delimiter "992" always comes after the lookup value.
How to tackle is ALWAYS: can you recognize a consistent patter. 100% of the time.
Thank you!!
You are welcome, Larissa!!!
👍👍👍
: ) : ) : )
Hi
Given Table 8*8, a table containing data of company sales across products country wise.
Since it is sales data of 8 products of the company for 8 countries.
I have been given three products in sheet 2 say product A ( cell reference B1), product B (C1) and product C (D1).
Cell A1 will contain the name of the country which I have to fill using formula.
Problem 1: I have to use a formula which will only fill the list of countries (starting from A2 cell to A8) which has a sale of product A or B or C only.
Problem 2: Say I have now only four countries from cell A2:A5. Now I have to get their sales in araay B2:D5 (for product A,B and C. I can get it using the lookup but I want to get it by only writing a dynamic array formula in B2 cell.
I have tried to understand what you are doing. But I have NO IDEA how to solve this because I can not understand what you are writing. I am sorry. There are people that are much smarter than me at interpreting what people ask. You can try the mrexcel.com/board web site to ask people that are much smarter than me.
what i have to do if ooking for character not numbers.?
If the extracted lookup value is text and it is being matched against text, it will work.
If you are not an MVP who else?, oh wait, maybe someone at Microsoft in charge to award MVPs? 😂
: ) : ) : ) : )
what about the regex solution? can you do a vid on that?
Soon. I still have to learn that new coding language.
What is going on with the revocation of your MVP?
Not sure. Did you see the video I posted 2 days ago: ruclips.net/video/CS2IS3Q8vQQ/видео.html
Why has your mvp status been revoked?
Your too good for Ms
Exactly the same question I was going to ask
They did not say. Did you see the video that I posted two days ago about this: ruclips.net/video/CS2IS3Q8vQQ/видео.html
Sir
Can you tell me how can I solve spill error on excel I face this issue
Spill error means there is data in the cells where the formula is trying to spill.
@@excelisfun
make a short videos about this 👏👏👏👏
i increase my excel skills by watcing your MESC 365 palylist
@@zeeshanking298 OK. I'll make a video for you in a few days : )
@@excelisfun
Thank you so much you are my Excel teacher I have learned a lot from your channel
First comment.
First place trophy to you!!!!
That's BS. You should have lifetime MVP status.
The good news is that I get to be lifetime Teammate to you and the rest of the Team and keep posting. It's just too much fun : )
Hi Mike, nice trick with "+0"/"x1", but what other reason than less typing you have that you dont use VALUE()?
=XLOOKUP(VALUE(MID(E3,5,6),$C$3:$C$14,$B$3:$B$14)
VALUE is a great way to do it. Less typing - that is why i do it.
Now if you want a dynamic start search "/", but what if length varies? , still thinking about that🙄🙄
Yes, if the position changes, then SEARCH or FIND : )
@txreal2 I can't reply to your comment because YT rejects my replies (I don't know why). So I'm trying to post this completely separate comment.
Excel related tricks, no problem :-)
But this time it will only apply for the "Insider preview" version of Excel 365 (because of REGEXEXTRACT function).
First, a simple case (like in Mike's video).
=XLOOKUP(--REGEXEXTRACT(E3:E26,"\d{6}",0),$C$3:$C$14,$B$3:$B$14)
and this applies to slightly more complicated cases like "654321/555555/7201002546954WA sreet PND-Exempt Wages"
=XLOOKUP(--REGEXEXTRACT(E3:E26,"\/(\d{6})\d*\s*[A-Za-z]+",2),$C$3:$C$14,$B$3:$B$14)
😁
Thanks for the REGEX fun, Bill Szysz!!!! I still not have learned that new coding language...