Hi Gasper. Great tips! Thanks for sharing them. On your 7-Flash Fill example, another way to extract w/formula if you have O365 is: =MID(E5,MAX((MID(E5,SEQUENCE(LEN(E5)),1)=" ")*SEQUENCE(LEN(E5)))+1,LEN(E5)) or like Unirotovibe below: =--RIGHT(SUBSTITUTE(E5," ",REPT(" ",LEN(E5))),LEN(E5)) with a double-unary instead of TRIM. Fun to solve in multiple ways :)) As always, many thanks! Thumbs up!!
Hi, thank you for all these great videos! Maybe another formula to extract the characters after the last space in a cell : =TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",LEN(E5))),LEN(E5))) Keep on the good work!
Great! You should share more of that quality of life hacks more often, Gasper! About paste special, you can press ctrl+alt+v and it will open the paste special dialog box, so you can paste as values but also do some other things. :)
Thanks Stefan. Was thinking of doing more. That's why there were three parts to this series. Will definitely do "my top 10 shortcuts in excel" but am still struggling to find other possible titles to record with tips and tricks. Thanks for mentioning Ctrl+Alt+V. Not many people are aware of it.
For the Paste Values a little side note: your wiggle trick only works for pasting in situ. For pasting values anywhere I use right-click, V. This IMO is the fastest way to do it. I use Paste Values all the time, and I advise my users to use it, too. For me, I put it on my Stream Deck (Copy, too, so, it is an easy combo). For my users I let them add it to the QAT and let them use it as a single click (my users love using the QAT, but they hate having to remember shortcuts 🤷🏼).
That is a tricky question Shoaib. So with XLOOKUP you can return the first greater or first smaller value, and you can search from top to bottom or bottom to top. So I guess it all depends on what you mean when you say MAX value. If your data is sorted ascending, then searching from the bottom will give you the "max" from the values that satisfy the condition of matching the searched value.
@@ExcelOlympics examples I want to lookup highest apple cost from other sheet while other sheet has many and multiple different fruit cost. We cannot sort it. Where ever in other sheet Apple has highest cost should lookup. After it will check Orange highest cost.
Thank you for your videos!
Excel Olympics is the the treasure chest for me. I am in love with it!
Sounds surreal Olga. Thank you. It means a lot.
Hi Gasper. Great tips! Thanks for sharing them. On your 7-Flash Fill example, another way to extract w/formula if you have O365 is: =MID(E5,MAX((MID(E5,SEQUENCE(LEN(E5)),1)=" ")*SEQUENCE(LEN(E5)))+1,LEN(E5)) or like Unirotovibe below: =--RIGHT(SUBSTITUTE(E5," ",REPT(" ",LEN(E5))),LEN(E5)) with a double-unary instead of TRIM. Fun to solve in multiple ways :)) As always, many thanks! Thumbs up!!
Thanks Wayne. Alternative solutions are always welcome as they give a different perspective and a different way of thinking. The more the merrier 😀.
Hi, thank you for all these great videos!
Maybe another formula to extract the characters after the last space in a cell :
=TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",LEN(E5))),LEN(E5)))
Keep on the good work!
Thanks for adding that valuable input. Makes us all better!
I am loving your videos! So helpful! Thanks a lot.
Vau. I'm flattered Dennis. Thank you for the kind words!
Great! You should share more of that quality of life hacks more often, Gasper!
About paste special, you can press ctrl+alt+v and it will open the paste special dialog box, so you can paste as values but also do some other things. :)
Thanks Stefan. Was thinking of doing more. That's why there were three parts to this series. Will definitely do "my top 10 shortcuts in excel" but am still struggling to find other possible titles to record with tips and tricks.
Thanks for mentioning Ctrl+Alt+V. Not many people are aware of it.
Great video, allways learning something new from you. Thank you :)
Thanks Davor. See you on the next one 😀.
For the Paste Values a little side note: your wiggle trick only works for pasting in situ.
For pasting values anywhere I use right-click, V. This IMO is the fastest way to do it.
I use Paste Values all the time, and I advise my users to use it, too.
For me, I put it on my Stream Deck (Copy, too, so, it is an easy combo).
For my users I let them add it to the QAT and let them use it as a single click (my users love using the QAT, but they hate having to remember shortcuts 🤷🏼).
Thanks for this addition Geert as it makes the whole story more complete.
You can also copy elsewhere with the wiggle trick (using Ctrl), but I do like right-click V myself.
I am confused with the result of the Statistics. The max # is 984 why it shows 930? Did you change the data?
Can we find Max value by using xlookup if multiple values available in range from a particular lookup item.
That is a tricky question Shoaib. So with XLOOKUP you can return the first greater or first smaller value, and you can search from top to bottom or bottom to top. So I guess it all depends on what you mean when you say MAX value. If your data is sorted ascending, then searching from the bottom will give you the "max" from the values that satisfy the condition of matching the searched value.
@@ExcelOlympics examples I want to lookup highest apple cost from other sheet while other sheet has many and multiple different fruit cost. We cannot sort it. Where ever in other sheet Apple has highest cost should lookup. After it will check Orange highest cost.
In that case the Xlookup is not the function you are looking for. You want MAXIF
@@ExcelOlympics yes maxifs shining perfect fit in this situation. Thanks
Short keys and tricks make your work easy and fast. practice make you perfect.
Thanks again.
Exactly Shoaib.
That Alt tip will be useful! I am going to give the chart template tip a try too. Thanks!
You are welcome Grainne. May those tricks serve you well.