Split Text By Capital Letters in Excel using Power Query EMT 1617
ฝัง
- เผยแพร่เมื่อ 6 ก.พ. 2025
- Download Excel File from excelisfun: excelisfun.net...
Download Geert's solution: excelisfun.net...
Download Peter B's solution: excelisfun.net...
Learn how to split text by Capital Letter. See the huge array formula in an Excel Worksheet and then the simple Power Query M Code formula to accomplish this task. Learn about the Power Query M Code Functions: Table.AddColumn, List.Count, Splitter.SplitTextByCharacterTransition, List.Max, Table.SplitColumn.
Downloadable Excel File has Excel Worksheet Array Formula that works in Office 365.
I show some crazy old school formula popping up, power query is much easier than that, thanks You Mr. Mike
You are welcome, Ogwal!!!!
Power Query is really powerful, it can do almost everything, once again, Thanks for always providing to us great and useful videos
U are always the best Mr. Mike
Just having fun : )
Exceptional and detailed example of splitting the text with M code!
Yes, it is a bit easier than with Excel Formulas : )
Great video👍. Thanks Mike
You are welcome!!!
Excellent video, Mike.
Check out the solution in the video, Geert's solution using Transpose, or Peter B's solution using Dynamic Arrays. The files are available for download in the links below video. There is a fourth solution also, from bo, it is in the EMT1617 file : )
There’s always multiple ways to solve things :-)
@@GeertDelmulle That is why we have virtually infinite fun : ) : )
Dear Professor,
Thankyou for all the lessons from scratch and the education that you have been providing. I was worried,why I did not come to know of you,when I started my career.
I recommend your videos and it helps many of the economically poor students in India, wherein they cannot go for a separate class or buy books to learn such valuable lessons. These videos of your education, helps a lot .
Thankyou professor. In this world, where everything is currency based, your selfless service goes a long way and hats off to you for the same. Teachers like you are blessings in disguise for students.
Thank you for these lessons. You have help so many people. Your knowledge and passion for excel is endless. Thank you for teaching us for so many years.
Thanks Mike, You are the Master of Excel (PQ) and teaching :-))
Thank you, Rilke-Masterful Poet of Power Query : ) : ) : )
Pure GENIUS.
I would have never thought of that...
I would have tried to count the Capital letters, but we actually needed a count of "space-Cap" or maybe I would have tried a count of ("space letter" =uppercase(space-letter))
@@OakleyTurvey I thought of counting capital letters for this too, but Bill Szysz showed me this code which produced a less complicated formula.
Thank You Mike. Power Query Seems To Always Have A Solution.
I agree : ) As I wrote earlier:
Well, Power Query is a much more complete computer language (as compared the Excel Worksheet Functions) for performing data manipulation, and we can easily have tables, lists, functions, values and other objects in a cell so we can work with them... so there are probably fewer things that Power Query can NOT do with Data Manipulation than Excel Worksheet Formulas : )
That was awesome. Thanks Mike for this EXCELlent video
You are welcome for the EXCLlent video! Thank you for your most EXCELlent support : ) : ) : ) : )
Thanks Mike.
You are welcome, WRH : ) : )
Amazing as always, Mike!!
Hi - best TH-cam video channel on earth. Can you do a very condensed version of how to use the index function?
I have a number of comprehensive videos about INDEX and MATCH. They go together. It is hard to use INDEX without MATCH. I will consider making a "INDEX and MATCH video in 5 minutes" for you. But in the meantime, I have some pretty good videos with time hyperlinked tables of contents below the video here: th-cam.com/video/u-8D2G0y9EM/w-d-xo.html
Your videos are unreal. They're a huge help, thanks Mike
Happy to help!
Definitely interested in Mcode gonna have to watch that playlists. Thanks mike
You are welcome! M Code is very powerful : ) :)
Awesome! Mike! I have to bookmark this for future reference :)
Wonderful video, thanks for upload
You are welcome for the upload, Sachin : )
Beautiful, thank you so much Mike
You are welcome so much, Katerina : ) : )
Amazing and very useful tip Mike, thank you!
You are welcome, ExactProBi !!!
Thank you a lot Mike, this especially help me for analyzing my data.
I am glad that it can help, 3 Santchev! In what way do you need to split by capital letters in your data analysis?
ExcelIsFun I ensure payment of community health agents by an application of mobile money transfert. in beneficiaries database all name of agent ares range in a same cell whilst the application uses requires a name by column. in that case your tips is precious help
thank you for showing me the excel function. lol hopefully one day i can understand it. M-code seems really sensible its on my list to learn
The MSPTDA class I have posted here at excelisfun, videos 3-12 can teach you a lot about M-Code: th-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
Great transformation of inbuild function to dynamic one. I thought it would be great if this function had an advanced option to split into rows like some other split functions
Wawwwwwww Perfection of PowerQuery Thanks
Thanks ! Excel Magician!
You are welcome, Mohamed!!!!!
very well done
Thanks, Steven Nye!!!
Bookmarked! 😀
Thanks Mark
That Excel formula though o.O
I agree: Too crazy!!!
This is amazing, thanks Mike
You are welcome, Kamran B!!!!
Great Stuff Mike. Thanks :) :)
You are welcome, John!
Awesome M Code.
Glad you like it!!!
Amazing! The formula is really crazy, and PQ so easy! I wonder how to include polish letters like "Ś"?
I am not sure, but we know who would know: The Poet of Power Query in Poland and the World: Bill Szysz : )
@Malina to nic trudnego :-), po prostu zamieniasz listę {"A".."Z"} na uzupełnioną o polskie litery czyli {"A".."Z", "Ą","Ć","Ę","Ł","Ń","Ó","Ś","Ż","Ź"}
@@excelisfun Yes, i know this time.... but another time? We will see :-)))
Just change the list {"A".."Z"} to this one {"A".."Z", "Ą","Ć","Ę","Ł","Ń","Ó","Ś","Ż","Ź"} ..... and you can already take into account the Polish capital letters :-))
@@BillSzysz1 WOW : ) : ) So you just did all of English Capitals and then added the ones that are different from Polish, all in list syntax?
@@excelisfun exactly :-)))
Great one Mike as usually ! But i love too the creasy excel formula , now i like to do things the way you do them so even when i can fix problem easily with power query , i think to do them with formulas and vice versa 👍
Yes, I like to do them in multiple fun ways : )
Great!
Thanks, Teammate : )
Hi Mike
Brilliant post but, if you think that makes PQ look easy, I am not convinced. Working directly with the M-code, rather than through the user interface, the way you do, would push my abilities to their breaking point. These skills were not picked up overnight!
I do think you have underrated dynamic arrays though; I will send you a trial solution just in case you are inclined to review it (I realise there are many other calls on your time).
Okay.
Let me say it another way:
Power Query is a much more complete computer language (as compared the Excel Worksheet Functions) for performing data manipulation, and we can easily have tables, lists, functions, values and other objects in a cell so we can work with them... so there are probably fewer things that Power Query can NOT do with Data Manipulation than Excel Worksheet Formulas.
I would be happy to see your Formula for this. I am sure it is more efficient than mine.
Thank you, Peter! I had a step by step Array Formula solution also. It was in the downloadable workbook. I have attached it above. Yours is more elegant, though.
As I have spent a few years trying to learn the other computer languages in Excel (M Code and DAX), I guess I am falling to the dark side. But, each has there place and I am glad that I know some of each language. The formulas you did or I did are great when the data is in a worksheet and you need the solution to update. Power Query when you are getting data from an external place or are in Power BI, and DAX when we have big data, want the solution as a PivotTable or in Power BI.
In this case, from the dark point of view, I do find this M Code solution easier, even if my data is in an Excel Worksheet.
-- Mike "Excel Dark Sider" Girvin ; )
P.S. I have added your solution to the downloadable files in the links below the video.
I added your solution to the downloadable files and put a pinned note at the top of the comments.
That Crazy Array formula, though!... (8-o) :-)
And it is even al lot worse than you may realize: Mike used Dynamic Array functions (FILTER and SEQUENCE) in that crazy array formula, without them it would even have been more crazy! Talking about taking the “craziness” to the next level! Oh My! :-)
Well, we all know that old saying: “I’m not crazy, I’m an airplane” (well, at least Dutch speaking people know it :-).
We’ve got a new Excel-version of it now: “I’m not crazy, I just make Crazy Array Formulas” (well, at least Mike does :-).
I like it: I’m not crazy, I’m an airplane!!!!! I am not the only one who is crazy about Array Formulas, Bill Szysz is smarter than me about Array Formulas, and there are many people at the Mr Excel Message Board that are smarter than me with these Array Formulas - but that is what makes us a great Online Excel Team : )
Mike, don’t humble yourself! Next thing you’re gonna tell us is that the only thing you’re good at is making YT videos!
We’re not buying that! We’ve seen waaay too many of your videos and know better. LOL! ;-)
@@GeertDelmulle Nope. The truth is: 1) that I do know some things about Excel, but Bill Szysz and many others at Mr Excel Message Board are MUCH smarter than I am about Excel and 2) My talent is in telling stories to help make complicated things seem less complicated.
Wow, you are being modest here. You’ve got a lot of things going for you, though, e.g. you wrote the book on the subject. :-)
Is there anything PQ can’t do? Amazing Mike!
Well, Power Query is a much more complete computer language (as compared the Excel Worksheet Functions) for performing data manipulation, and we can easily have tables, lists, functions, values and other objects in a cell so we can work with them... so there are probably fewer things that Power Query can NOT do with Data Manipulation than Excel Worksheet Formulas... Lucky for us, Chris!!!!!!
One trick to deal with excess columns if you just put in a big number, is to select first column,
Unpivot other, this gets rid of nulls,, then select 'Attribute' and pivot, Just remember to select 'don't Aggregate in advanced options.
Magic thank you
You are welcome for the magic, BW Games!!!!!
Hi Mike, thank you for the beautiful solution. I noticed that when you refreshed at 7:29 a new column labelled Text.42 appeared... Why?
I am not sure, but I think it is because the Table in Excel had 5 columns, When Power Query had to split again in the Power Query window, it created 5 columns with different data, then when it went to load it, it saw the columns in the spreadsheet were different and that there were now two columns named Test 4, so it has to add the 2 at the end because it is not allowed to have two columns with the same name.
@@excelisfun Hi Mike, thank you for your answer!
@@excelisfun @fabiogambaro I believe it's something to do with the way they implemented Load-to-table and specifically the way they handle column insertion. I think that the insertion of a column on the lower table was done at a sheet column level, which had the side effect of affecting the upper table.
Mike,
Love this trick with editing to insert a space in the formula before an uppercase letter. But what about multiple spaces? Wanting to work with a Quicken report that displays Category, Subcategory1 and Subcategory2. They're indented like this in Column 1
Auto Expense
Chevy
Fuel
and the only one with a transaction we want to keep is the Fuel line
Is there a way to create this line:
Auto Expense/Chevy/Fuel
for importing into a proper data set?
Thanks!
amazing man
Magicien thank You
I mean magic
I like your word better: Magicien ; )
Great mike
Very handy technique. Could you have extracted the max count in the step "Added Column" without having to create the "MaxSplits" step so basically it's just one less step?
I do not know how, because we needed the Max as a separate step so we could use it later. I suppose that we could have put the whole max formula into the 4th argumnet of Table.SplitColumn.
Nice video. Bit of a niche requirement, but certainly makes you think about other uses. I think I probably prefer VBA regex for this type of thing. In fact, I think Power Query could possibly have benefited from adding regular expressions in a nicer fashion.
It seems that you can download Python and R and run code in those languages for regular expressions. A bit out of my comfort zone, but I might give it a try sometime! I've always quite fancied getting into data science, but don't know where to start, so this might be an opportunity!
@@ricos1497 Goo thinking all around, Rico S!!!!
I do not have the latest PQ update with the new added split functions. If I add the code in advance editor will this still work? In theory I’m thinking yes but wanted to ask. Thanks.
I do not know. I don't have an older version to try it with. You should try, then report back : )
Hi, Mr Girvin can you please answer my question
i have recently stated watching your videoes can you tell me which playlist is most suitable for MCSA BI Reporting.
Your Fan From India.
I do not know what "MCSA BI Reporting" is : (
What is "MCSA BI Reporting"?
@@excelisfun sir, its a certification exam conducted by Microsoft .
www.microsoft.com/en-us/learning/mcsa-bi-reporting.aspx click on this link.
Pls reply
@@prateekmathur2071 I am sorry, but I do not have videos that prepare people for certification.
Hi. I have a question. Can i the same method use when i want to expand a table with Variable number of column? Thank you
Mike, how long did it take you to write that crazy long Excel formula?!
Power Query is clearly very powerful but is lacking in user friendliness in places. The documentation is often horrible as is the Intellisense.
I totally agree with you about: 1) "documentation is often horrible". I mean, come on, Microsoft, you just wrote the code to make it do what it does, and you can't write the help to illuminate the infinite power and beauty of M Code!?!? They deserve to go bankrupt for making such a basic business decision blunder. 2) "the Intellisense" is a joke. Wait, if you hit tab to accept the item from the drop down, it deletes most of the formula!?!?! Yes, we can type a space to avoid this, but come on, Microsoft. Power Query is the greatest tool since the PivotTable and virtually on Excel users use it because it is too intimidating...
It took a long time. Not just one day. I gave up on the first try, but two days later i just had an idea how to finish it...
It is worth learning Power Query because it increases the level of infinity that Excel provides us : ) : ) : ) : )
@brianxyz I’m not saying PQ is perfect in terms of user interface, but you should try Power Pivot. In my experience that is a lot worse.
I like the PQ interface because it lowers the learning curve tremendously, IMO.
@@GeertDelmulle I agree. For me, DAX is by far the most difficult. I mean, to learn it deeply, it seems harder than Excel Formulas or M Code. DAX is just to invisible, and there are insane hidden things going on that are not obvious. M Code syntax takes a while to learn, and there are so many functions, but at least with M Code, different than with Excel Worksheet Formulas, there is a function for almost anything, and the functions have smart names to them. Because Microsoft built so many functions in M Code, for seemingly every situation, we have to do less indirect functions mashing, like we have to do in Excel.
All my data sets are in excel so replacing text-to-columns is one of my primary uses for PQ. Text-to-Columns R.I.P!!!
No, no, not so fast: Convert Text dates and numbers with Alt, A, E, F is still better than anything else : )
I agree and i use it when i need that functionality. But most transformations that used to use T2C now are accomplished with PQ@@excelisfun
@@richardhay645 Yes, indeed, I agree: virtually all from T2C and now PQ : ) : )