I just added text file for Power Query example. Text file for Power Query example (right-click to download): excelisfun.net/files/06-M365ExcelClassTextFile.txt
@@excelisfun YW i always find new Excel knowledge all over youtube however your videos are really straight to the point, condensed and helpful .. please keep sharing!
You’re the master of teaching, Mike!… Whenever anyone asks me “how do you spell ‘comprehensive’”, I always say: you spell it with the letters from ‘Mike Girvin’. Then -predictably- the discussion ensues: “but there’s no “g” in there…” And then I insist they watch one of your videos. Then they come back to me and say they understand… :-)
Greatly done! You're definitely our most wanted Entertrainer. Go Mike, go! 👏👏👏 the most important I learnt this time that I should use CONCAT() instead of CONCATENATE(), I didn't realize this replacement.
@@RogerStocker CONCAT lets you select a range and join into one cell. In this way it is an aggregate function. CONCATENATE can not do that - if you select a range it is a function argument array operation and will deliver multiple answers. CONCAT is a slick new function that speeds up joining multiple items without a delimiter.
Thank you Mike , I have read In the absence of a separation mark If we set 1 in match_end , the result of the formula will be the original content I apologize for the translation match_end = 1 7:00 C39 : =XLOOKUP(TEXTBEFORE(TEXTAFTER(B39;"-");"-";;;1)+0;B45:B46;C45:D46) =TEXTBEFORE(text; delimiter; instance_num; match_mode; match_end; if_not_found)
I didn’t know about DOLLAR and FIXED. You Excel at teaching. Sorry about the bad pun. When matching/ comparing values where one is a text number and the other is a number number, is it better to convert the text to number or number to text?
Love the bad pun ; ) I love FIXED too - I use it often. DOLLAR will show negative numbers as accounting parentheses, which is sorta annoying. It does not really matter whether you go text to number or number to text if they both are single items. However, if one side is a single item and one side is an array, like in our example. converting the single item side will take less calculation effort on Excel's part. For example, this would work: =XLOOKUP(TEXTBEFORE(TEXTAFTER(B39,"-"),"-"),B45:B46&"",C45:C46), but they the conversion would happen on the side with many items, rather than on the side with one item.
Hi is there a formula or PQ way to change any transactions time that come between 12:00:00 AM and 7:59:59 AM To be 8:00:00 AM which is the start of working hour.
O, Yah. That will be taught in a future MECS video. I can't wait for those advanced videos, But gotta make it through all the foundations first... Glad you found it, Jerry!!!
Thanks Mike, I still do not have the new text functions but they are awesome. I learnt a lot in your video. Two remarks: DOLLAR is actually not a good name, it should be CURRENCY because if I use it, the value converts to EURO. In text analysis, I also use the * (asterisk) as a great helper...😉
Very good point about DOLLAR. I never use it if I might get negative numbers because it shows them in accounting parentheses. Yes, the wild card for zero or more characters is a real hot tip!!!!
Excellent Video Mike, thank you for this great explanation. For the text split, I think I have another solution to split Carlota/658:West by using FilterXML as follow: =TRANSPOSE(FILTERXML(""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A19,"/"," "),":"," ")," ","")&"","//R"))
Good stuff as always! FIXED & DOLLAR were new. 👍 I wonder why you didn't use PQ's "clean" function at the end. It handles multiple spaces between words. Or does it only handle char32s and NOT 'tab's or char160s?
I could not get Text.Clean to work on multiple spaces between words. Help says: Text.Cleam returns a text value with all control characters of text removed. As I know it, it just returns line feeds (Alt + Enter in Excel Worksheet, or CHAR(10) using the CHAR function in Worksheet). There might be a way to use it, but I am not sure how.
Also, I must apologize for the slowness in posting MECS videos, but my family has had a violent tragedy, which I posted about in UA-cam Community Tab area on my excelisfun channel home page about 10 days ago...
@@excelisfun Mea culpa. You're so right! (No surprise). It's right next to "trim" and I only ever read "between words" and assumed incorrectly. Thanks!
Great video Mike. I haven’t done much text manipulation recently and that was a great refresher- also I learned a new function- fixed!! Brilliant and I have a use for it.
Those were some great examples! You turned an extract text example into an extract after, extract before, convert to number, lookup and spill the return array example. Also, textsplitting with multiple delimiters, I don't know where else I would have seen that, if not for ExcelIsFun TEXTSPLIT(B57,{"/",":"}). There was techniques I've never used, like at 23:53 where you showed the =MID(J114,SEQUENCE(LEN(J114)),1) and =CODE(L115#). That was awesome! I learn so much from the great in-depth examples. Thank you Mike.
Awesome as ever. Thanks for this Mike. The Substitute trick to remove those unwanted spaces has been a real chore for me in the past. Always having to copy, the character out and use FIND AND REPLACE. Thanks for this Mike
I am so glad that my story telling works : ) Noting like getting up each day and trying to tell stories to make complicated things less complicated : )
I depend on helpful Teammates like you : ) I make many mistakes and when Teammates like you help me to track them down, it helps our Team. I have added link below video and here: Text file for Power Query example (right-click to download): excelisfun.net/files/06-M365ExcelClassTextFile.txt
Now a days more demanding for data analytics please make a video on it. 1. Basics 2. Right Inner Join 3. Left Inner Join 4. Right Outer Join 5. Left Outer Join 6. SUM 7. SUMX 8. CALCULATE 9. PICARD 10. COMPARE 11. DRILL UP 12. DRILL DOWN 13. COLLAPSE 14. EXPAND 15. COUNT 16. RECOUNT 17. COUNTX 18. COUNTROWS 19. COUNTUNIQUE 20. ROW LEVEL SECURITY 21. MIN 22. MINA 23. MAX 24. MAXA 25. CALCULATED COLUMN 26. CALCULATED MEASURE 27. USAGE OF FILTER 28. SLICER 29. UNDERSTAND HOW TO CREATE A MEASURE TO CALCULATE YOY GROWTH 30. UNDERSTAND HOW TO CREATE A MEASURE TO INCREASE THE VOLUME IN SOME GIVEN DATASET BY SOME % 31. MEASURE TO CREATE MAT VOLUME 32. CALCULATED TABLE 33. UNDERSTAND HOW TO CREATE A DATE TABLE STARTING FROM 2015 TO 2017 34. ADD COLUMN FUNCTION 35. SUMMARIZE FUNCTION 36. UNDERSTAND ABOUT CONDITIONAL LOGIC 37. UNDERSTAND ABOUT FORMAT FUNCTION
I have almost all that. Start Here: ua-cam.com/video/TjSnQ4VDHTE/v-deo.html Then this video has over 50 videos with most of what you want: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
I have 500 other videos about data analytics also. Are you subbed? Do you know where my home page is: ua-cam.com/users/ExcelIsFun (Search this page for what you want) , but above playlist has most of it
That was a great video Mike. You went over a whole bunch of very useful text formulas, I especially like the =MID(J114,SEQUENCE(LEN(J114)),1) along with the Code formula. Thank you Mike!
what function do I need to use to split 1.Abia State (Umuahia)? the 1 should be in one cell, Abia State should be in another cell and (Umuahia) should be in another cell
Hi Michael, a suggestion if I might add: could you title each spreadsheet in the workbook? I understand the abbreviations are used to keep the tabs short when navigating, but sometimes I forget, for example, that worksheet "TWF" stands for "Text Worksheet Formulas" when referring back to the lessons on my own. Thanks!
I have struggled with this mightily over the last two decades. I have 1000s of videos and workbooks. And about half are long descriptive names and the other half are short... I am not sure how to reconcile my oscillation lol I will keep it in mind moving forward, but the oscillation is bound to re-surface again : (
Worth mentioning TRIM is also the fastest function to convert numbers to text (4 letters, no other arguments). Useful e.g. if you lookup numeric fields and your lookup table is text (e.g. account in a chart of accounts)
@@excelisfun I didn't mean faster ad in calculation time over large dataset (i'd use PQ for that) but in time to enter (trim(A4) as opposed to text(A4,"#") - lots of shift+sth in the latter :p
@@dziadeck47 O I see... I thought you had discovered a cool new speed thing lol back in the 10s (2010 - about 2020), I timed a lot of formulas and found some surprising results. But that is what my second book was about...
@@excelisfun I meant the entire table from excel to powerpoint. When it's copied, lot of spaces before number copied which create formatting issue in each cell of powerpoint.
@@excelisfun yeah mate I was broke and unemployed and thanks to your free lessons I'm building a career now, I see you as a true benefactor, I will never forget that. can't thank you enough.
I just added text file for Power Query example. Text file for Power Query example (right-click to download): excelisfun.net/files/06-M365ExcelClassTextFile.txt
Hi there, any chance you can update the Excel file link?Thanks!
@@11chance Unfortunately, it may until Monday or Tuesday until the site is back up. We had a catastrophic error and it will be a few days... : (
@@excelisfun awesome, didn’t expect your speedy reply. Your are champion and hope you enjoy your rest of weekend! 😄
@@11chance : )
MECS => Mike, Excel's Coolest Scientist ✌😉
Thanks for the clever MECS!!!! : )
Absolutely 👍
Amazing!!!! Thanks Mike :) :)
You are welcome, Formula Guy John!!! : )
I got your new book in Amazon because of your vids. Currently studying to become a data analyst. Wish me luck 😉
I wish you great luck, MeLuv U1Tym!!!!
lots of gems here... i follow many channels but this the real deal...
Thanks for your kind words, Snake Eyes!!!! Glad you like the gems : )
@@excelisfun YW i always find new Excel knowledge all over youtube however your videos are really straight to the point, condensed and helpful .. please keep sharing!
@@snakeeyesOFFICIAL76 I will!!! Because it is so much fun ; )
You’re the master of teaching, Mike!…
Whenever anyone asks me “how do you spell ‘comprehensive’”, I always say: you spell it with the letters from ‘Mike Girvin’.
Then -predictably- the discussion ensues: “but there’s no “g” in there…” And then I insist they watch one of your videos.
Then they come back to me and say they understand… :-)
Thanks, Geert!!!!! This is a LOT of fun text formulas all in one place ; )
Mike, quick question: what does the Excel Classic CLEAN function do to those non-breaking spaces?
(A: nothing)
Greatly done! You're definitely our most wanted Entertrainer. Go Mike, go! 👏👏👏
the most important I learnt this time that I should use CONCAT() instead of CONCATENATE(), I didn't realize this replacement.
Yes, CONCAT is great when you want to join with no delimiter. Great new function.
@@excelisfun It's not really new, as I used CONCATENATE() since decades. There is no difference I guess, is there?
@@RogerStocker CONCAT lets you select a range and join into one cell. In this way it is an aggregate function. CONCATENATE can not do that - if you select a range it is a function argument array operation and will deliver multiple answers. CONCAT is a slick new function that speeds up joining multiple items without a delimiter.
@@excelisfun I got it now CONCATENATE() spills the vertical range, while CONCATE() does a real concatenation. 👍thanks a lot.
Thank you Mike ,
I have read
In the absence of a separation mark
If we set 1 in match_end , the result of the formula will be the original content
I apologize for the translation
match_end = 1
7:00
C39
:
=XLOOKUP(TEXTBEFORE(TEXTAFTER(B39;"-");"-";;;1)+0;B45:B46;C45:D46)
=TEXTBEFORE(text; delimiter; instance_num; match_mode; match_end; if_not_found)
I had not heard that, but it makes sense : ) Thanks for the tip!!!
I didn’t know about DOLLAR and FIXED. You Excel at teaching. Sorry about the bad pun.
When matching/ comparing values where one is a text number and the other is a number number, is it better to convert the text to number or number to text?
Love the bad pun ; ) I love FIXED too - I use it often. DOLLAR will show negative numbers as accounting parentheses, which is sorta annoying. It does not really matter whether you go text to number or number to text if they both are single items. However, if one side is a single item and one side is an array, like in our example. converting the single item side will take less calculation effort on Excel's part. For example, this would work: =XLOOKUP(TEXTBEFORE(TEXTAFTER(B39,"-"),"-"),B45:B46&"",C45:C46), but they the conversion would happen on the side with many items, rather than on the side with one item.
Wish you 1M soon Mike Sir. you are master blaster in Excel.
Glad you7 like the master blasting, SACHIN!!!!
Hi is there a formula or PQ way to change any transactions time that come between 12:00:00 AM and 7:59:59 AM To be 8:00:00 AM which is the start of working hour.
This is a fantastic compilation Mike - very helpful, THANK YOU!
You are welcome, IreneH!!!!
Thanks for all
is very completed to be learn
thanks for knowledge
thanks youtube
You are welcome for all, Rusdi!!
Just found your "ShowFormulas" LAMBDA function. Nice! 😉
O, Yah. That will be taught in a future MECS video. I can't wait for those advanced videos, But gotta make it through all the foundations first... Glad you found it, Jerry!!!
Thanks Mike, I still do not have the new text functions but they are awesome. I learnt a lot in your video. Two remarks:
DOLLAR is actually not a good name, it should be CURRENCY because if I use it, the value converts to EURO.
In text analysis, I also use the * (asterisk) as a great helper...😉
Very good point about DOLLAR. I never use it if I might get negative numbers because it shows them in accounting parentheses. Yes, the wild card for zero or more characters is a real hot tip!!!!
Hi , Mike please help me
Total is 32
how to divide 32/3
3
3
3
3
3
3
3
3
3
3
2 (Balance is two is last row)
32 (Total)
Thanks Mike. Yet another fantastic video. Always appreciated.
Glad you appreciate them, Matt!!!!!
Excellent Video Mike, thank you for this great explanation. For the text split, I think I have another solution to split Carlota/658:West by using FilterXML as follow:
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A19,"/"," "),":"," ")," ","")&"","//R"))
Yes!!!!! Old School FILTERXML is fun : ) : ) Thanks, Nader!!
Sir can you teach how to analyse Stock Data to identify Higher High and Higher Low and Lower Low and Lower High
Thanks, if I can work smartly, all credits are to you. You are my guru
I am glad that I can help : )
Thank you for sharing the knowledge, Mike. You are always awesome.
Glad you like the shared knowledge, El Badlis!!!!
Thanks Mike for this EXCELlent video.
You are welcome, Fellow teacher : ) : ) : )
Good stuff as always! FIXED & DOLLAR were new. 👍
I wonder why you didn't use PQ's "clean" function at the end. It handles multiple spaces between words.
Or does it only handle char32s and NOT 'tab's or char160s?
I could not get Text.Clean to work on multiple spaces between words. Help says: Text.Cleam returns a text value with all control characters of text removed. As I know it, it just returns line feeds (Alt + Enter in Excel Worksheet, or CHAR(10) using the CHAR function in Worksheet). There might be a way to use it, but I am not sure how.
Also, I must apologize for the slowness in posting MECS videos, but my family has had a violent tragedy, which I posted about in UA-cam Community Tab area on my excelisfun channel home page about 10 days ago...
@@excelisfun
Mea culpa. You're so right! (No surprise). It's right next to "trim" and I only ever read "between words" and assumed incorrectly.
Thanks!
@@excelisfun No worries! It's a big deal what happened. Take care of your family.
Go Team!!!
So information and fun video at the same time. Loved it 🥰🙏 Thank you sir
8:59. Good day. May i ask if there is any othr method to auto detect extra space. Likeauto trim spces in case of large data.
Great video Mike. I haven’t done much text manipulation recently and that was a great refresher- also I learned a new function- fixed!! Brilliant and I have a use for it.
FIXED is a great secret for sure ; )
Those were some great examples! You turned an extract text example into an extract after, extract before, convert to number, lookup and spill the return array example.
Also, textsplitting with multiple delimiters, I don't know where else I would have seen that, if not for ExcelIsFun TEXTSPLIT(B57,{"/",":"}).
There was techniques I've never used, like at 23:53 where you showed the =MID(J114,SEQUENCE(LEN(J114)),1) and =CODE(L115#). That was awesome!
I learn so much from the great in-depth examples. Thank you Mike.
In-depth is lots of fun, and power in our hands to solve any problem : ) : ) : )
Awesome as ever. Thanks for this Mike. The Substitute trick to remove those unwanted spaces has been a real chore for me in the past. Always having to copy, the character out and use FIND AND REPLACE. Thanks for this Mike
You are welcome, Temidayo!!!!
I watched your previous videos on text formulas and you have really enriched this one. You are the best.
Yes, this MECS is the most enriched class I have posted to date : )
You are brilliant with excel Mike! Thank you very much! Big hug!
You are welcome much! Big Hugs Boomeranged Back at You, Luciano!!!
Spell check in edit mode was a new tool. Thanks and very nicely illustrated.
I'm such a bad speller: I get burned all the time with spelling errors in text formulas. F7 in edit mode saves the day!
What's the perfect way to copy excel decimal numbers to powerpoint. Thanks for these amazing videos
Copy and paste. That is it. Unless you were asking a different question with different parameters.
I learned a lot of new text formulas and tricks. Thank you Mike.
You are welcome, kiwikiow!!!!!
Great video as always. Thanks Mike
The TRIM scénario is so Amazing
Glad you like it, Thierno!!!!
Very informative video sir I love it...
Glad you love it, Budy!!!!
Boom!Super Fun Class,Always Great To Pick Up Some Awesome Tips And Tricks...Thank You Mike :)
You are welcome, Bike Brother darryl!!!! You already new most tricks though, being an Excel Master as you are ; )
You are amazing! Nobody else explains Excel like you do…
I am so glad that my story telling works : ) Noting like getting up each day and trying to tell stories to make complicated things less complicated : )
Thank you very much sir.
You are welcome!
Definitely an EPIC video! I think Im getting to be an expert at PQ!
Awesome, Andrew!!!! There is a LOT more about Power Query. You are going to love it : )
Hello Mike,
Unable to locate the txt file for cleaning in PQ.
I depend on helpful Teammates like you : ) I make many mistakes and when Teammates like you help me to track them down, it helps our Team. I have added link below video and here:
Text file for Power Query example (right-click to download): excelisfun.net/files/06-M365ExcelClassTextFile.txt
@@excelisfun Thank you Mike. Love your videos and way of teaching.
simply wonderful Sir
Glad it is wonderful for you, tanveer!!!!
Now a days more demanding for data analytics please make a video on it.
1. Basics
2. Right Inner Join
3. Left Inner Join
4. Right Outer Join
5. Left Outer Join
6. SUM
7. SUMX
8. CALCULATE
9. PICARD
10. COMPARE
11. DRILL UP
12. DRILL DOWN
13. COLLAPSE
14. EXPAND
15. COUNT
16. RECOUNT
17. COUNTX
18. COUNTROWS
19. COUNTUNIQUE
20. ROW LEVEL SECURITY
21. MIN
22. MINA
23. MAX
24. MAXA
25. CALCULATED COLUMN
26. CALCULATED MEASURE
27. USAGE OF FILTER
28. SLICER
29. UNDERSTAND HOW TO CREATE A MEASURE TO CALCULATE YOY GROWTH
30. UNDERSTAND HOW TO CREATE A MEASURE TO INCREASE THE VOLUME IN SOME GIVEN DATASET BY SOME %
31. MEASURE TO CREATE MAT VOLUME
32. CALCULATED TABLE
33. UNDERSTAND HOW TO CREATE A DATE TABLE STARTING FROM 2015 TO 2017
34. ADD COLUMN FUNCTION
35. SUMMARIZE FUNCTION
36. UNDERSTAND ABOUT CONDITIONAL LOGIC
37. UNDERSTAND ABOUT FORMAT FUNCTION
I have almost all that.
Start Here: ua-cam.com/video/TjSnQ4VDHTE/v-deo.html
Then this video has over 50 videos with most of what you want: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
I have 500 other videos about data analytics also. Are you subbed? Do you know where my home page is: ua-cam.com/users/ExcelIsFun (Search this page for what you want) , but above playlist has most of it
Wonderful tutorial 🎉
👏
Glad you liked the text fun, Emre!!!!
How to extract Excel file from zip folder? In power query
I have no idea. I always unzip file before pointing Power Query to a folder.
That was a great video Mike. You went over a whole bunch of very useful text formulas, I especially like the =MID(J114,SEQUENCE(LEN(J114)),1) along with the Code formula. Thank you Mike!
Thank you so much for your videos. They are awesome.
You are welcome!!!
Is TextSplit available? I thought it was in beta earlier
It is in all of M 365 Excel, as far as I know. I hope you don't have Excel 2019 or Excel 2021... Only M 365 has everything.
what function do I need to use to split 1.Abia State (Umuahia)? the 1 should be in one cell, Abia State should be in another cell and (Umuahia) should be in another cell
At 32:27 when I press spacebar twice to remove double spaces. It will remove both char 160 and char 32 spaces right? Or is it only char 32 spaces?
Hi Michael, a suggestion if I might add: could you title each spreadsheet in the workbook? I understand the abbreviations are used to keep the tabs short when navigating, but sometimes I forget, for example, that worksheet "TWF" stands for "Text Worksheet Formulas" when referring back to the lessons on my own. Thanks!
I have struggled with this mightily over the last two decades. I have 1000s of videos and workbooks. And about half are long descriptive names and the other half are short... I am not sure how to reconcile my oscillation lol I will keep it in mind moving forward, but the oscillation is bound to re-surface again : (
@@excelisfun No worries, I understand! It's a minor problem anyhow & I can just make a note for myself. Thank you for the videos as always!
@@aznapwned You are welcome for the videos. But I feel your pain and I never know what to do about it... : (
Fantastic as usual
Glad you like it, N Lover!!!!
Thank you👍
You are welcome, Davor!!!!
ITS AMAZING
Glad it is so!!!!!!
=textafter &=texbefore is not showing in my excel why?????????
The GOAT!
I am glad that you liked the video, 7Kiya!!!!!
Great work Mike.
Glad you like this!!!
Great video. Very efficient. "Lean Cuisine": Good Meat; No Fat!! TRIM successfully applied!
TRIM is dual: diet and hair cuts. I made this joke about TRIM, but it got cut when I was editing... Darn editors lol
Character 32, Character 160.
Yes!!!!!! 32 and 160 never seemed so fun lol
Worth mentioning TRIM is also the fastest function to convert numbers to text (4 letters, no other arguments). Useful e.g. if you lookup numeric fields and your lookup table is text (e.g. account in a chart of accounts)
Have you timed TRIM against cell&"" to see if it is faster?
@@excelisfun I didn't mean faster ad in calculation time over large dataset (i'd use PQ for that) but in time to enter (trim(A4) as opposed to text(A4,"#") - lots of shift+sth in the latter :p
@@dziadeck47 O I see... I thought you had discovered a cool new speed thing lol back in the 10s (2010 - about 2020), I timed a lot of formulas and found some surprising results. But that is what my second book was about...
@@excelisfun I only have the new one :) Guess with the new engine in 365 a lot may have changed (not buying the other one ;p )
@@dziadeck47 You are lucky : ) : ) The old one was very complicated compared to today.
You the best
I am so glad to help, Samuel!!!!
Nice tricks for excel
Thanks!
Thank you for your kind donation, N Sanch01!!!!
Nice
Glad you like it, SAT!!!!!
First comment sir
First Place Trophy for you, Sawai!!!!! : )
The Text Formula Master strikes again. Thanks Mike!
@@chrism9037 You are welcome, Chris M!!!!!
What's the perfect way to copy excel decimal numbers to powerpoint. Thanks for these amazing videos
Copy and paste. That is it. Unless you were asking a different question with different parameters.
@@excelisfun I meant the entire table from excel to powerpoint. When it's copied, lot of spaces before number copied which create formatting issue in each cell of powerpoint.
@@yashrsingh Paste as Picture.
so awesome
Glad it is awesome for you, Alberto!!!!
@@excelisfun yeah mate I was broke and unemployed and thanks to your free lessons I'm building a career now, I see you as a true benefactor, I will never forget that. can't thank you enough.
PRODUCT NAME REPEAT TIME
TATA 4
MARUTI 3
HERO 2
RESULT
TATA
TATA
TATA
TATA
MARUTI
MARUTI
MARUTI
HERO
HERO
=TOCOL(IF(SEQUENCE(,MAX(Count))
Thanks 🙏🙏🙏🙏👍👍👍
@@anil11996 You are welcome, long time Teammate Anil!!!