Great video, cool tricks!!! For fun I came out with a formula to help visualizing the characters in a compact way, by columns also. =LET(show,1,vala,8000,valb,11500,c,20,m,MIN(vala,valb),x,MAX(vala,valb),r,INT((x-m+1)/c)+1,s,SEQUENCE(r,c,m),u,UNICHAR(s),IF(show,s&" "&u,u)) where variables are: show , 1 or 0 , if 1 will see in every cell also the unicode nr. next to the character, if 0 only the character(use 0 to see them more clearly, use 1 to make notes for the fav ones) vala and valb , values of unicode nr , lower and upper limits we want to visualize, no particular order c, nr of columns we want to visualize across. Playing with the variables we can explore much easier and we can make notes with the ones we want to remember for future use.
Hey Mike. For text+character analysis nerds like myself, this is a great video. I had no idea there were over 143K unichars! I've been using the CHAR and ALT+xxx shortcut for decades. Thanks for this. I will have to deep dive into the UNICHAR function.
Being an old timer, I love ASCII - signatures, pictures, and of course using as here to get useful characters. Cool vid! Great way to get them quickly & easily. [Regarding the download link not working, copied the link text and that worked fine, but uses http instead of https.]
Hi Mike. Awesome as always! Thanks for the Sunday buffet of delicious functions: SEQUENCE, CHAR, LET, CHOOSE, UNICHAR, ADDRESS, ROW, COLUMN and FORMULATEXT :)) Always a surprise and something new to learn at ExcelIsFun! Thumbs up!!
Yeyyy, I love your content. After 1 minute I could insert the 64 hexagrams of the i ging using unichar(number). Don`t ask how much time I waisted before trying :)
@@excelisfun RUclips’s algorithms is strange. I get more recommendations from channels I just started watching but haven’t subscribed to yet than I get from channels I’ve been subscribed to for years. I’ve found a few tricks including the bell one I mentioned that sometimes work
@@patrickschardt7724 I used to get a lot of notifications, but then they all stopped. Every one of them. It made me so mad at RUclips. Why would they do that?
That’s a good one! Ever since Mr. Excel revived the ASCII codes (using the ALT-key) I’ve been using bullets in Excel ever since. And of course, leave it to Mike to take it to the next level... and then some. BTW: congrats on breaking the 700K subs limit! :-)
Hi Mike, I owe you a lot in term of not only learning excel, but enjoying it and come up with solutions suites my work needs, I have a request if you can do PMP- Excel related series as the field rich , in different specialisty for finance to Human resources, quality, business almost every thing. I hope you can do that and I am sure if you did a lot of audience will be over the moon. Thank man I am forever grateful for the knowledge you spread with inspiration and dedication.
May the 12 months of the New Year be full of new achievements for you. May the days be filled with eternal happiness for you and your family! Wish You Happy New year! Regards Vijay
Thanks for another awesome video! 😍 Something in the context of all the symbols .. There is a keyboard shortcut in Windows of all emojis - I use it a lot in conditional formatting and especially in data validation lists : Windows logo key + . (Point) Usable in all OFFICE software and all Windows
For the uber-lazy, inserting =CHAR(SEQUENCE(255))in cell A1 spills the list of characters and lets the row numbers index the character list. A really cheap "single cell" formula!! LOL. Since I earned my "lazy-user" merit badge years ago, I eagerly set up my CHAR list this way shortly after DAs were first introduced to Insiders!
1. (00:00) Introduction. 2. (00:43) SEQUENCE function with 255 3. (01:10) Spilled Array Formulas 4. (01:19) CHAR function for ASCII characters 5. (01:36) Spilled Range Operator # 6. (01:37) LET function to spill two column report. 7. (03:00) SEQUENCE function with 143859 8. (01:19) UNICHAR function for Uni characters 9. (03:26) Formulas if you do not have Microsoft 365 Excel using ROWS function. 10. (04:00) Bonus Formula to show cell reference and formula when a cell contains a formula. Text formula uses Uni characters and the UNICHAR function. 11. (06:00) Move Spilled Array Formula 12. (06:22) Summary, Closing and Video Links
Sir, will you please a upload a video on "How to get unique records from rectangular range through formula?" I mean to say, suppose you have some names on Range("A1:E5"). Now, You have to find out the unique values out of those range through formula. Please don't use dynamic Array formulas. Because, We don't have Office 365. So, we have no other way but to rely on old formulas. Please Sir, Will you make a video on this topic?
That is really hard to do. I wrote a whole book about extract unique list and sorting formulas back in 2012, but the formulas are off the chart complicated. Here is playlist of free videos from my book: ruclips.net/p/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci Videos 16-20 show the techniques for odler versions. However, none of the videos show how to do a rectangular range. To inisitate a dialog to help get you a solution, try mrexcel.com/forum Amazing excel question site.
Interesting on the Unichar - never explored that. Thanks. Off topic question: right now Power BI has some nice query functions for getting through a webpage that PQ Excel does not. I tried to copy the query from Power BI into PQ and it didn't work because of these functions. I also tried using Power BI files as a source for PQ and couldn't find a way to do this. The only thing I could figure out is creating a report in Power BI, refreshing it, saving it as a PDF and using that as a PQ source. Am I missing something easy? I have a lot of work in the Excel PQ file that I don't want to change over in Power BI and it would be nice if I could just hit refresh to get it all to work in Excel. Thanks in advance.
There are a bunch of PQ features and DAX formulas that are only in Power BI. Power BI is a better over all tool for data, visualizations and some kinds of reporting. But, Excel can do some many other things too that Power BI can never do, that some of us old timer Excel people still do too much in Excel that we probably should be doing in Power BI.
@@excelisfun It would be nice if you could import the tables from BI into PQ Excel as a data source just like you can import Excel data into BI. Then use Power Automate to make it work seamlessly.
Mike sir the workbook is not opening, when click it to open the excel workbook hangs up the computer. Please help sir, so that I may follow along the video and see the file..
I think you are right, but I stared created text formulas WAY before TEXTJOIN was invented, so it is sort of second nature. In this case I only had a few spaces so I just did it manulayy, however, if I had a bunch to join, I would switch to TEXTJOIN. Thanks for the tip, Joel!
Can someone suggest a particular excel message board? I am struggling to develop a formula that extracts the value of the cell just to the right of an hlookup result. For example, if the lookup result is in C4, I need the value in C5. Thanks.
@@peltiertech1879 Well, we know that forever, we have new wonderful Excel Nuggets to uncover, whether from the past of new. I never run out of new and useful things that I bump into. It is one of the things that makes Excel so much fun!!!
Fantastic, thanks Mike...I hope the weather is good enough for you guys to get out riding today.
No, we have not ridden in a few weeks. LOTS of rain. I hope soon!
Amazing and excellent. Thank you.
You are welcome, K D!!!!!
SEQUENCE with CHAR/UNICHAR...neat!
Cool, Doug : )
Great video, cool tricks!!! For fun I came out with a formula to help visualizing the characters in a compact way, by columns also.
=LET(show,1,vala,8000,valb,11500,c,20,m,MIN(vala,valb),x,MAX(vala,valb),r,INT((x-m+1)/c)+1,s,SEQUENCE(r,c,m),u,UNICHAR(s),IF(show,s&" "&u,u))
where variables are: show , 1 or 0 , if 1 will see in every cell also the unicode nr. next to the character, if 0 only the character(use 0 to see them more clearly, use 1 to make notes for the fav ones)
vala and valb , values of unicode nr , lower and upper limits we want to visualize, no particular order
c, nr of columns we want to visualize across.
Playing with the variables we can explore much easier and we can make notes with the ones we want to remember for future use.
Amazing!!! Thanks, Teammate. I just added it to the workbook : )
Wow! This is really awesome and very clever! Thanks for sharing it :))
@@wayneedmondson1065 Thank you!! You're very welcome!!✌
Wow. That's awesome. Thanks Mike for this EXCELlent video.
You are welcome fellow teacher Syed MM : ) : )
Hey Mike. For text+character analysis nerds like myself, this is a great video. I had no idea there were over 143K unichars! I've been using the CHAR and ALT+xxx shortcut for decades. Thanks for this. I will have to deep dive into the UNICHAR function.
Yes, UNICHAR just expands our creative ability with Text Formula!!!!! Always glad to hang out with a fellow text+character analysis nerds!!!
Being an old timer, I love ASCII - signatures, pictures, and of course using as here to get useful characters. Cool vid! Great way to get them quickly & easily.
[Regarding the download link not working, copied the link text and that worked fine, but uses http instead of https.]
Thank you very much!!!!!!!!! I added the s to http and the link is working.
Hi Mike. Awesome as always! Thanks for the Sunday buffet of delicious functions: SEQUENCE, CHAR, LET, CHOOSE, UNICHAR, ADDRESS, ROW, COLUMN and FORMULATEXT :)) Always a surprise and something new to learn at ExcelIsFun! Thumbs up!!
You are welcome for the fun Sunday buffet, Wayne : )
Amazing and super useful topics
Glad it is helpful, Tech Santosh!!!!
Great video and great trick. Thanks.
You are welcome, Wojciech!!!!
loved it
Awesome! Love is good : )
Thank you very much. Very interesting and usefull
Glad it helps, Alaor!!!
Awesome. Thank you.👍
You are awesomely welcome!!!
Yeyyy, I love your content. After 1 minute I could insert the 64 hexagrams of the i ging using unichar(number). Don`t ask how much time I waisted before trying :)
I almost have to laugh Mike, coz the amount of times I google looking for ASCII and its front of me all the time lol. Thank you, brilliant 😀
Yes, it is crucial for us Excel people to be able to figure out which character is which... Glad you like it, Paul!!!
Mike, I feel like you've been hitting a whole new stride!
Really? Cool! What is the stride that you see?
@@excelisfun LET and the more recent LAMBDA seem like they've been good fuel for you. You've been doing a great job of carrying us all forward.
@@matthewzarembski2063 Yes, sir!!!! It is so much fun and that is why I can try and help carry the Team forward !!!!!!!
@@matthewzarembski2063 And everything spilled, it is also a great new frontier that inspired me to try and help our Team move forward : ) : )
Very very cool Mike!
Thanks, Chris : ) : ) : )
How did I miss this one? Thanks RUclips for not notifying me of one of my favorite creators
I have the same problem, RUclips does not notify me for any of my subscriptions, even with the bell icon.
@@excelisfun I’ve found turning the bell off and back on sometimes helps to get notifications working again
@@patrickschardt7724 I will try that. Shame on RUclips for making it so hard...
@@excelisfun RUclips’s algorithms is strange. I get more recommendations from channels I just started watching but haven’t subscribed to yet than I get from channels I’ve been subscribed to for years. I’ve found a few tricks including the bell one I mentioned that sometimes work
@@patrickschardt7724 I used to get a lot of notifications, but then they all stopped. Every one of them. It made me so mad at RUclips. Why would they do that?
Boom!Really Happy Just Learned Some Real Cool Tips...Thank You Mike :)
You are Boom Really Cool Welcome, darryl : )
That’s a good one! Ever since Mr. Excel revived the ASCII codes (using the ALT-key) I’ve been using bullets in Excel ever since.
And of course, leave it to Mike to take it to the next level... and then some.
BTW: congrats on breaking the 700K subs limit! :-)
You are welcome for the ASCII next level! And thanks for the thanks about 700 : )
Nice Mike ,
Thanks and happy holidays 🎄
Glad you like it, Sevag!!
Happy holidays to you too : )
Hi Mike,
I owe you a lot in term of not only learning excel, but enjoying it and come up with solutions suites my work needs, I have a request if you can do PMP- Excel related series as the field rich , in different specialisty for finance to Human resources, quality, business almost every thing. I hope you can do that and I am sure if you did a lot of audience will be over the moon.
Thank man I am forever grateful for the knowledge you spread with inspiration and dedication.
Very Interesting.
Glad you like it, SIMFINSO!!!
Very interesting
Glad you like it, Abdul!!!
Thanks Mike. That was a Great Video. BTW, all the best for the holidays to you and all!! :) :)
You are welcome! Happy Holidays!!!!!
May the 12 months of the New Year be full of new achievements for you. May the days be filled with eternal happiness for you and your family!
Wish You Happy New year!
Regards
Vijay
Thanks for another awesome video!
😍
Something in the context of all the symbols ..
There is a keyboard shortcut in Windows of all emojis - I use it a lot in conditional formatting and especially in data validation lists
:
Windows logo key + . (Point)
Usable in all OFFICE software and all Windows
LOVE this: Windows logo key + . (Point) Thanks for the hot tip : )
For the uber-lazy, inserting =CHAR(SEQUENCE(255))in cell A1 spills the list of characters and lets the row numbers index the character list. A really cheap "single cell" formula!! LOL. Since I earned my "lazy-user" merit badge years ago, I eagerly set up my CHAR list this way shortly after DAs were first introduced to Insiders!
Thanks for the look lazy-user version : )
I love it ♥ ☺ :)
Go Team!!!!
1. (00:00) Introduction.
2. (00:43) SEQUENCE function with 255
3. (01:10) Spilled Array Formulas
4. (01:19) CHAR function for ASCII characters
5. (01:36) Spilled Range Operator #
6. (01:37) LET function to spill two column report.
7. (03:00) SEQUENCE function with 143859
8. (01:19) UNICHAR function for Uni characters
9. (03:26) Formulas if you do not have Microsoft 365 Excel using ROWS function.
10. (04:00) Bonus Formula to show cell reference and formula when a cell contains a formula. Text formula uses Uni characters and the UNICHAR function.
11. (06:00) Move Spilled Array Formula
12. (06:22) Summary, Closing and Video Links
Wow... Almost every symbol I need is in the list of unichar
Yes!!! It is an amazing list : )
Sir, will you please a upload a video on "How to get unique records from rectangular range through formula?" I mean to say, suppose you have some names on Range("A1:E5"). Now, You have to find out the unique values out of those range through formula. Please don't use dynamic Array formulas. Because, We don't have Office 365. So, we have no other way but to rely on old formulas. Please Sir, Will you make a video on this topic?
That is really hard to do. I wrote a whole book about extract unique list and sorting formulas back in 2012, but the formulas are off the chart complicated. Here is playlist of free videos from my book: ruclips.net/p/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci Videos 16-20 show the techniques for odler versions. However, none of the videos show how to do a rectangular range. To inisitate a dialog to help get you a solution, try mrexcel.com/forum Amazing excel question site.
This is just what I needed! Thanks!! Ps is there a way to create a dynamic translation tool using the inbuilt excel translator?
I am not sure : (
@@excelisfun No troubles! Something for all of us to tinker with and to see if it’s feasible.
Interesting on the Unichar - never explored that. Thanks. Off topic question: right now Power BI has some nice query functions for getting through a webpage that PQ Excel does not. I tried to copy the query from Power BI into PQ and it didn't work because of these functions. I also tried using Power BI files as a source for PQ and couldn't find a way to do this. The only thing I could figure out is creating a report in Power BI, refreshing it, saving it as a PDF and using that as a PQ source. Am I missing something easy? I have a lot of work in the Excel PQ file that I don't want to change over in Power BI and it would be nice if I could just hit refresh to get it all to work in Excel. Thanks in advance.
There are a bunch of PQ features and DAX formulas that are only in Power BI. Power BI is a better over all tool for data, visualizations and some kinds of reporting. But, Excel can do some many other things too that Power BI can never do, that some of us old timer Excel people still do too much in Excel that we probably should be doing in Power BI.
@@excelisfun It would be nice if you could import the tables from BI into PQ Excel as a data source just like you can import Excel data into BI. Then use Power Automate to make it work seamlessly.
Hi, I guess there's a typo with "Character" :)
[Update thumbnail spelling re character]
Thanks for the help! I always need help from Teammates like you because I am such a bad speller. I have fixed it : )
Mike sir the workbook is not opening, when click it to open the excel workbook hangs up the computer. Please help sir, so that I may follow along the video and see the file..
Hit F5 key to refresh the browser, then try again. Or you can right-click and save it that way.
@@excelisfun Ok Sir many thanks
Instead of using the &" "& in the formula why not use TEXTJOIN? I think that makes formulas easier to read.
I think you are right, but I stared created text formulas WAY before TEXTJOIN was invented, so it is sort of second nature. In this case I only had a few spaces so I just did it manulayy, however, if I had a bunch to join, I would switch to TEXTJOIN. Thanks for the tip, Joel!
Can someone suggest a particular excel message board? I am struggling to develop a formula that extracts the value of the cell just to the right of an hlookup result. For example, if the lookup result is in C4, I need the value in C5. Thanks.
Yes, this is the best I know: mrexcel.com/forum
The Download liink is not working..)
I am not sure why Chrome is causing issues with download. If you right-click and download it works, or try a different browser.
Here's what I learned: there's a function called IFNA(). When did that come out? And please don't say 2007.
Okay, I won't Jon : ) I think it came out 2013. It is a REALLY useful function, as I am sure you immediately realized once you learned of it!
@@excelisfun It was in fact 2013. I've been using IF(ISNA(blah blah)) for far too long.
@@peltiertech1879 Well, we know that forever, we have new wonderful Excel Nuggets to uncover, whether from the past of new. I never run out of new and useful things that I bump into. It is one of the things that makes Excel so much fun!!!