Go Power Query!!!!!!! Thanks, K D : ) : )

@@excelisfun You are welcome..

Yes, there seems to be nothing, for data, that Power Query can not do!!! Thanks for stopping by, as always, Chris : ) : ) : ) : )

Yes, the old math operator trick : ) But your fav, Power Query, is THE tool when it comes to dealing with data : ) : ) : )

You are welcome for the EXCELlence, Syed MM : ) : ) : ) : )

You are very welcome, Anji!!!!

Nice to see you, Sir!!!! ExcelVbaIsFun : ) : ) : ) : )

@@excelisfun So nice to be seen! lol! You are the man, Mike!! Thanks so much for being awesome!

You are welcome, Malina : ) : )

Yes, 700, Ctrl + E, Done!!!

You are welcome! I love to hear that you share examples with your students! For over 12 years I have made all the content available for free to help spread Excel Education around the world : )

You are welcome, ashish!!!!

Yes, all is good! I love them all, each in there own way ; )

Thanks for liking every method, Ashok : ) : ) : ) : )

You are BOOM welcome, darryl : ) : ) : ) : )

You are welcome!

That makes three of us agreeing, more too : ) : ) : ) : )

You are welcome for the help, Sheri!!!

LOVE that keyboard, Sachin ! Thanks : )

Yes! Power Query is the #1 answer to anything to do with Data : ) : ) Thanks for commenting, Mahesh!!!!

Thank you, formula guy, John Borg : ) : ) : ) : )

Glad you like that last one, Nahidul!!!!!

Thanks, Teammate Excel Aprende!!!

Power Query in THE best magic tool when it comes to data : ) Thanks for stopping by in the comments, Abdullah!!

You are welcome for the formula, Ajitkumar!!!!

You are welcome, Luciano!!!

Thanks, Wayne!!! Those are great formulas : ) I have added them to the downloadable Excel File! You are right about the great fun all around : ) : ) : )

@@excelisfun Thanks Mike! After posting, I tinkered with solving via a single Dynamic Array Formula.. as in: =--LEFT(B5:B10,FIND(" ",B5:B10)-1) to get the ID and so on for the other abstractions. So.. even more fun if you have Office 365! Thumbs up!!

Did you mean EXCELlent ; ) You are welcome for the videos, ContrastY!!!

Yes, Power Query is THE tool when it comes to data : ) : ) Thanks for stopping by in the comments, Yaser!!

That is a good assessment! It is so cool that something so old, TTC, is sometimes the perfect thing!!

Glad that you like the stories that I tell. It is fun to make them. Thanks for stopping by in the comments, Matt C : ) : )

Lucky for us, for sure : ) : )

I love it, Meni : ) : ) : ) : ) Thanks for more fun for the Team!

Well, since Excel is infinite, then yes, the fun will never end!!!!!

I am glad it is good for you, Dan!!

That is epic, O Masterful One : )

I just added your formula to downloadable workbook file : )

For this example, me too! Thanks for stopping by in the comments, Rash kk : )

Glad it is super for you, Tulsidas!!!!!

Yes, formulas are the last option. You are welcome as always, Matt!!!!

As we both know, that does not always happen... Then we do something different, which of course, Excel accommodatingly lets us do : ) : )

Tq!!!!!!!

The Rad Branch : ) Glad you like the videos, J M!!!

Formulas and TTC in any version : ) Thanks for stopping by in the comments, Clovis!!!

That is a great idea. I bet they do have something up there sleeve!

@@excelisfun as long as it allows a number attribute to return the Nth item of the array, then I'll be happy. Until I complain about something else of course! Like why isn't there a function for combining two arrays into a single array? Or two or more arrays into an array of all possible combinations? Honestly, I don't know what those guys do all day!

@@excelisfun In the meantime, in the spirit of solving a problem a day, a "simple" let formula allows us a SPLIT function, sort of (by replacing _string with your text, and _findChar with the character you want to find): =LET(_string,A1, _findChar," ", _seq,SEQUENCE(LEN(_string)), _chr,MID(_string,_seq,1), _posOfFind,IF(_chr=_findChar,_seq,0), _posOfFindDesc,SORT(_posOfFind,,-1), _noOfFinds,SUM((_posOfFind>0)*1), _seqNoOfFinds,SEQUENCE(_noOfFinds,1,_noOfFinds,-1), _removeZeros,INDEX(_posOfFindDesc,_seqNoOfFinds), _seqNoOfFindsPlus1,SEQUENCE(_noOfFinds+1), _lastChr,IF(_seqNoOfFindsPlus1=_noOfFinds+1,LEN(_string),INDEX(_removeZeros,_seqNoOfFindsPlus1)-1), _firstChr,IF(_seqNoOfFindsPlus1=1,1,INDEX(_lastChr,_seqNoOfFindsPlus1-1)+1+LEN(_findChar)), _textLen,_lastChr-_firstChr+1, _split,MID(_string,_firstChr,_textLen), _split)

You are welcome!

Glad to charm you with formulas, Hussein!!!

I am sorry, but I do not understand your question. Can you ask it a different way?

@@excelisfun Sorry. Can the VBA code and M Code be combined in one Code, or should we only use one of them? Thanks in advance.

@@beginfatlossDOTcom I do not know : (

I tried your formula, but it doesn't work for me.

Thanks for the REALLY cool formula, Software Train. I just added it to the downloadable Excel File : ) : ) : ) : )

@@Ingolf00001 , If you download the Excel file I added the formula to the Formula (an) sheet. You can check it out there.

That is so crazy, I do not even understand it?!?!?!?!!? : ) : )

Thanks, gr cr0912!!!!! I just added it to the downloadable File : ) : ) : ) : )

@@excelisfun SUBSTITUTE function creates the XML format that FILTERXML understands. Because of this special syntax formula is aware of the elements that are embedded and separated in the string format and you can extract them. You're welcome !!😊

@@Excelambda Thank you for that explanation, cr gr0912 : ) : )

My rule is simple: I use Flash Fill when the pattern is 100% consistent (like before first space, and it is a quick one time event) and I need one column. Then I love it. But you are right, Flash Fill can be a mess because it can't see many patterns... Power Query is worthy of best friend-ness : ) Thanks for stopping by in the comments, Cassius!!!

Code like this might work: let Source = Excel.CurrentWorkbook(){[Name="fSales"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Amount", type number}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Product", Order.Ascending}, {"Amount", Order.Descending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Product"}, {{"Top03", each Table.FirstN(_,3), type table [Product=text, Amount=number]}}), #"Expanded Top03" = Table.ExpandTableColumn(#"Grouped Rows", "Top03", {"Amount"}, {"Amount"}) in #"Expanded Top03" Where we sort the full table, then use Group By amd Table.FirstN.

@@excelisfun Thank you very much!

@@radu_sirbu You are welcome! You can always help me to further my goal of bringing free Excel Education To The World with your comments and thumbs ups on each video that you learn from, and of course your Sub : )

That is a good point. I can not tell you how many times in my 25+ years of Excel that I go and change something and them lament that the solution did not update... Thanks for stopping by and commenting, Dennis D!!!

For free back and forth dialog try: mrexcel.com/forum For paid consulting e-mail me at excelisfun at gmail.

@@excelisfun i have sent you email sir in detail with "Need Help" subject please do reply me there.

Here is the 2 minute intro video to the Excel Basics class: kzfaq.info/get/bejne/bdh8hr2KzuCboXk.html Here is the full class: kzfaq.info/sun/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k But here is probably the video you want, which is the 2 minute video that introduces you to the excelisfun channel and shows how to find whatever you want: kzfaq.info/get/bejne/opddZMSHzKzYmGg.html

@@excelisfun thank u for replying. Would make best out of this quarantine time.

@@AnujSharma-wt8bw , Yes, in that way we can make good come out of bad!

Mac Excel is much different. Many of the tools, like Power Query, are not even in Mac Excel. This is a sad historical fact. Even I started my Excel career in the early 1990s with Mac Excel, but soon learned that in the working world I had to give up my Mac Excel and use Windows PC Excel to access many of the useful tools...

Sort me too. At least I make my self do them too, so I don't forget how : ) Thanks for stopping by, Sam Sami!!!!

Yes!!!!!! Power Query does anything with data : ) : ) : ) :)

Thanks, Phone Excel : ) : )

@@excelisfun Come live once.For American people & For Rest of World because of Day & Night Difference in the Different Countries.

Yes, Power Query gives us the most powerful tools for "anything data"! Thanks for stopping by, DRSteele!!!

Those ARE more complicted, but they can get the job done : ) Thanks, Khaled : ) : )

Flash Fill is REALLY good when the pattern is exact : ) : ) Thanks for stopping by in the comments. Mr. Brownstone!!!