Love to hear that your Monday is made better, sherry izzie : )

Go Team!!!

Glad all the vids help!!!!

Cool : )

You are welcome!

Always glad to help, Nader!!!

It is nice to boomerang back : )

You are welcome for the more beauty and less gloom, Tom Jones!!!

Glad to boomerang back, even if it is at half speed, Fabio!!

Thanks, Friend Bill Szysz : ) : ) : ) : )

Yes, Power Query seems even more infinite than Excel Worksheet formulas : )

I am glad to be back, K D : )

Glad to be back, Abdul!!!

Glad you like it, Teammate cr gr0912!!!

You are welcome, Shakira!!!

Awesome!! Thanks for the formulas, M Smith : )

Glad to boomerang back, J DO!!!

Playing in Excel is good!!!

Sad for the old school...

You are welcome as always, Matt!!!

Glad to boomerang back and make videos for the Team : )

Glad you like it, Luciano!!!

Glad it is GoOoOoOoOoOoOd for you, Ankur!!!

You are welcome, Amador!!!

Glad you like it, Sevag!!!

Me too. Me Too. Maybe someday Microsoft will allow Power Query & DAX functions in the worksheet?

You are welcome, brother!!!

You are welcome for the wicked Boom Fun Video, darryl : )

Yes, lists in PQ are love-worthy : )

Yes!!! LET there be fun : )

Thanks for the double comments, K D : ) : )

Yes: Power Query Thumbs Up : )

You are welcome a lot, Maan!!!

Alwasy glad to help with cleverness, Hoi Tin Wan!!!

Yes!!! Good Start Monday : )

Glad to amaze, Rash !!!

You are welcome log time WRH : ) : ) : ) : )

Glad you like it, Sachin!!!

Yes, Sunday fun is a good way to come back after a two week absence... Half speed, but at least the boomerang still works : )

@@excelisfun Glad you're back!

@@wayneedmondson1065 Lucky to be back : )

@@excelisfun Glad to hear you are on the mend :)) Best wishes for a speedy and full recovery!!

Glad it is Owesome for you, Nitin!!!

Yes, back at half speed, but happy to back with the Team : )

@@excelisfun Old School?? =CONCAT(MID(B5,MODE.MULT(IFNA(MATCH(ROW(INDIRECT("1:"&LEN(B5))),(1-(ISNUMBER(--MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))))*(ROW(INDIRECT("1:"&LEN(B5)))),{0,0}),"")),1)) Just Playing about. I'm sure that you can shorten it to the better. : ) : ) in D5 : ) : )

SUBSTITUTE(TEXTJOIN(", ",,MID(B5,MODE.MULT(IFNA(MATCH(ROW(INDIRECT("1:"&LEN(B5))),(1-(ISNUMBER(--MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))))*(ROW(INDIRECT("1:"&LEN(B5)))),{0,0}),"")),1)),", ","") Maybe??

You are welcome, Chris : ) : )

You are welcome so much, Rahul!!

Cool, nitesh!!!!

@@excelisfun ❤

You are welcome, anand!!!

I am sorry but I do not understand your question : (

Glad you like it, Mahesh !

Excel Worksheet formula like this can work to convert SAP Date to Serial Number Date if the Regional settings are set to dd/mm/yyy: =SUBSTITUTE(D9,".","/")+0 If your regional settings are different, then maybe: =DATE(RIGHT(D9,4),MID(D9,4,2),LEFT(D9,2)) In Power Query, you can use the Data Type icon, then "Using Locale", then choose location, like French (France) or whatever your location is.

@@excelisfun thank you very very much sir for prompt reply.

@@ManishKumar-qu7ou : )

Yes, the old standby SUBSTITUTE 10 times : )

That was my first idea, too. And it makes what most of the solutions do, but looking carefully there are differences, the flash fill does not exact the same because it also eliminates special characters like { or *. So depending what you want as result, you cannot use all variants for the same outcome.

Yes, clearly much of what we used to do with VBA, expecially in regrads to data, can be done with Power Query. That is by design from Microsoft : )

LOVE it!!!!!

To elaborate! I would always lay out the let formula as if you were writing code, then you can return back through each step to evaluate. As a long winded solution to your problem: =LET(_alphanumeric,A5, _seq,SEQUENCE(LEN(_alphanumeric)), _charsAndNos,MID(_alphanumeric,_seq,1), _chars,IF(ISERROR(--(_charsAndNos)),_charsAndNos,""), _finalText,TEXTJOIN("",TRUE,_chars), _finalText) At any point you can test each step by replacing _finalText with _charsAndNos, or _chars or whatever, which will return the result of that step regardless of the subsequent entries in the formula. I assumed this would be best practice to make it more readable (each step documented almost) and reusable (simply copy and change A5), but now you've got me thinking.... To Let or not to Let. I don't know anymore.

@@ricos1497 Definitely best practice, but I think for some Excel formulas it may be less practical. But you are right, listing each step makes the debugging less difficult and lays everything out to see. Yes, using CONCAT around LET was sort of a "sleepy" mistake on my part, that is why I added the comment with the CONCAT on the inside : )

@@excelisfun someone should do a deep, philosophical video on the let function and what it'll mean for the future of Excel. I think it could be an existential threat to the thin formula bar. I believe we could be headed for a pop-up formula bar that can be thrown about like a size and properties pane; expanded and then dumped in the corner of your second monitor. Everyone will be writing 35 line let statements instead of just =Left(A1,5). It'll be like the wild west. Of spreadsheets. Incidentally, do you have a slightly uneven table these days (I'm assuming you're in a different working environment during lockdown)? Your keyboard sounded louder than normal, suggesting a vibration on a less than flat surface. Just an observation!

@@ricos1497 , Philosophically and for purity, we probably should use the LET like you say, defining every variable and every par on a new line. But I think that is impractical in some cases. Even in the Power Query let statement, each line sometimes has many nested steps. Even a simple task like Test.Remove([Data],{"0","9"}) has two parts: the creating the list and the removing part. So it will be a balance. I know that I am not as pure as you. I might be too much of a mega formula guy. I see some of the elegant variable definition by Marco Russo in DAX and by you and Bill Szysz with LET, but I am not sure I will be as pure. I will be as pure as defining all repeating variables. But I will keep an open mind moving forward, especially with such great Teammates as you, Rico S, Bill Szysz and Marc Russo : ) BTW, I will make a philosophical and fun video about LET in the next few months. P.S. I have a big announcement later today and the philosophical LET video will be part of that... : ) : ) : ) : ) Thanks for being a great Teammate and helping me to learn, Rico S!!!

@@excelisfun you're too kind! An announcement. Exciting. Hopefully I'll still be awake in the UK. Is it that Microsoft will be releasing amazing new features in Excel, available only on office 366, being released only for the leap year?

Glad to be back, Apples Oranges!!!!

Cell B1: =MID($A1,COLUMNS($B2:B2),1), then copy to side. If you have Microsoft 365 Excel, then just this in B1: =MID($A1,COLUMNS($B2:B2),1)

@@excelisfun Thanks Mike. That was a quick fix.

Hi Here is another solution if you are using Microsoft 365 =MID(A1,SEQUENCE(,LEN(A1)),1) Let me know it works for you...

@@deexcelguy Thanks. That was helpful.

I do not know how to use an array in SUBSTITUTE, But 10 Nested SUBSTITUTES will work : )

Happy Monday, ashish! For queries, I am not able to answer now, try: mrexcel.com/forum

@@excelisfun ok

@@excelisfun Mike no issue

Hi Mike good morning Hope you are doing fine. I have a question If i have mix data Where we have "text number and special character" so how can we extract special character from those particular string. This is very easy from VBA function programming,but is there any way to extract special character,kindly share with me solution,because i know u have solution. Thanks in advance Ashish

Me, too. ;-)

@@GeertDelmulle Double funny : )

FILTER does rule : )

Thanks for your swag comment, Kokneiy : )

Very nice, Lampros!!!

Thanks for Sharing, Lampros!

1st and 2nd!!!! Yes : )

Yes, better to use SUBSTITUTE 10 times...

1st Place trophy goes to you!!!

Sharp Excel Fun, Jonathan : )