Thanks for showing how to remove numbers from text. That was 5 great examples. It was neat when adding the double negatives to the MID function --MID(A5,SEQUENCE(LEN(A5)),1), to get the value errors. I can see that being very helpful for things. You rock Mike! Thank you.
Welcome back Mike. This is excellent and practical as always! I was thinking in my job, I would be more interested in removing text and keeping the numbers. So I experimented and found that in the Excel 365 formula all you have to do is put NOT in the include argument of the FILTER function wrapped around the ISERROR. =CONCAT(FILTER(MID(A5,SEQUENCE(LEN(A5)),1),NOT(ISERROR(--MID(A5,SEQUENCE(LEN(A5)),1))))) Wrapping the ISERROR in NOT reverses the true and false. in the Excel 2016 function, same thing. =TEXTJOIN(,,IF(NOT(ISERROR(--MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))),MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1),""))
Welcome back Mike. Amazing video! I learn a lot from you everyday ! Watching ur old videos as well. They are all amazing as they help me a a lot at work.
For dynamic data, my first choice was Power Query until you demonstrated the new Let function. It's great to have options. Flash fill is awesome in the right application. Thanks Mike. Always appreciated.
Hello mike another easy way without formula is by using find & replace (ctrl +H )..first select ranges & find 0 to 9 & replace each find with blank by replace all option.
Boom!POWER QUERY Rocks!!Been Really Impressed With The LET Function What A Great Addition,Still Love FLASH FILL Where Possible..Wicked Tutorial Thank You Mike :-)
ExcelIsFun, Mr. Mike, what about bit more complex problem, Let say i do have mixed invoice numbers with text. and I want to keep text with decimal separator (comma or dot) and decimal number? Can you make a video about it?
Hi Mike.. love this.. the Office 365 formula is definitely cool or cooly or COOL or COOLY :)) Like the PQ solution too.. Text.Remove().. thanks for the good learning there. As always.. Thumbs Up for your thought provoking and challenging problems and solutions! Super Sunday fun at ExcelIsFun!! PS - Hope MS adds normal F9 evaluation capability to LET.
@@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 : ) : )
COOLY! Old school is good to know, so that we do appreciate the easiness brought by Flash Fill / Power Query. So solve this problem, PQ should be the preferred way! :) Isn't it?
Thanks a lot Mike. Recently I am using below VBA code separate number from text through multiple looping. But i think PQ is just next level. Sub For_Next_Loop_in_Text() Dim i As Long 'for looping inside each cell Dim myValue As String Dim NumFound As Long Dim TxtFound As String Dim r As Long 'for looping through rows LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = StartRow To LastRow myValue = Range("A" & r).Value For i = 1 To VBA.Len(myValue) If IsNumeric(VBA.Mid(myValue, i, 1)) Then NumFound = NumFound & Mid(myValue, i, 1) ElseIf Not IsNumeric(Mid(myValue, i, 1)) Then TxtFound = TxtFound & Mid(myValue, i, 1) End If
Next i Range("H" & r).Value = TxtFound Range("I" & r).Value = NumFound NumFound = 0 TxtFound = ""
Next r
End Sub Sub Clear_Values_For_Text_Loop() LastRow = Range("A" & Rows.Count).End(xlUp).Row Range("H" & StartRow, "I" & LastRow).ClearContents End Sub
Hi, i am following you.. i want a video on excel which will allocate leads to employees evenly among the team. Condition is that leads of same location to be employees of same location by excel formulas.
Controversial use of LET there Mike! Putting it within another function. That's crazy. I'd never do that. You're wild. I'd always put it as the final step in the function. In other words, create a variable for your unconcatenated list and then concat that. Also, it makes debugging easier as you can switch your output between your variables to check each step.
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?
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.
So if we let the array fall in different cell and if we use concatenate function in the last cell and use concatenate(range) and press F9 in the range then it will hard code the resultant values of cell in {"a","b"} and then remove the {}.
Hello master, please I have a concern how to split a column that contains dates and amounts into two separate columns (dates and amounts) with power query.
and also is there really no way to make the check box bigger doesn't matter what size of text i use the check box itself is so tiny compared to the columns
is it possible to make the row skip 2 numbers so i can have a locked row 2 with all my highlight names. and row 1 to have the columns in where i can search after the names in that row. eksl i want it to say row 1 and 2 the one to the left not column A but the row numbers so Row 3 will be row 1 and first to call them like 0 and 01
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.
Hey Mike. I have a variation on this video which I'd like to see a solution for...Suppose I have number e.g. "72816493" and I want to show each individual numeral in a separate column - what formula will do this? i.e. 72816493 in cell A1 will be broken up into numerals 7 in cell B1, 2 in C1, 8 in D1, 1 in E1, etc.
Hi Mike, I found another way to extract only numbers or only text. To extract numbers I input =TEXTJOIN("";TRUE;IFERROR(MID(A5;ROW(INDIRECT("1:"&LEN(A5)));1)*1;""))+0 To extract text I input =TEXTJOIN("";TRUE;IF(ISERROR(MID(A5;ROW(INDIRECT("1:"&LEN(A5)));1)*1);MID(A5;ROW(INDIRECT("1:"&LEN(A5)));1);"")) If I had to extract the capital letters from a text containing no numbers at all I input =TEXTJOIN("";TRUE;IF(EXACT(UPPER(MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1));MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1));MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1);"")) I believe will be helpful for many. Bye.
I've become lazy ever since I learned about the Power Tools. I'd use PQ (Power Query) and call it a day. It looks like a clean up operation, anyway. :-)
Wow mike welcome back ......monday is going good with ur priceless knowledgeable shared video thanks Mike And plzz check ur inbox i sent you one query,whenever u have time kindly go through and reply with me solution. 👌👌👌👌👌💐💐
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
Hi Mike, here we go =SUBSTITUTE(CONCAT(MID(0&C4,SMALL(ISTEXT(IFERROR(--MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1),"#"))*ROW(INDIRECT("1:"&LEN(C4))),ROW(INDIRECT("1:"&LEN(C4))))+1,1)),0,"") Inspired from your videos . 🙏🏿🙏🏿🙏🏿 Thank you
Mike - awesome video. Been watching you for 6-7 years and still come back time and again to learn new tricks!
Glad all the vids help!!!!
Mike you are the master of these formula combinations!
Go Team!!!
Omg……welcome back, Mike! You surely make my Monday more enjoyable:)
Love to hear that your Monday is made better, sherry izzie : )
Such a wonderful nested function (Excel 365). Thanks Mike ! Hurrey.... Office 365 !!!!!
You are welcome!
It's good to see you healthy. :-))) I began to worry when you were away for over a week.
Thanks for many useful solutions.
Thanks, Friend Bill Szysz : ) : ) : ) : )
Thanks for showing how to remove numbers from text. That was 5 great examples. It was neat when adding the double negatives to the MID function --MID(A5,SEQUENCE(LEN(A5)),1), to get the value errors. I can see that being very helpful for things. You rock Mike! Thank you.
Cool : )
Welcome back, Mike, your videos are wonderful and inspiring!
It is nice to boomerang back : )
Mike, as usual, you make my days more beautiful.
Even this gloomy Monday came to light after watching your post.
Thank you.
God bless you!
You are welcome for the more beauty and less gloom, Tom Jones!!!
Flash Fill Is My favorite The Easiest and less complication
thank for your effort
You are welcome, Shakira!!!
Welcome back Mike. This is excellent and practical as always!
I was thinking in my job, I would be more interested in removing text and keeping the numbers. So I experimented and found that in the Excel 365 formula all you have to do is put NOT in the include argument of the FILTER function wrapped around the ISERROR.
=CONCAT(FILTER(MID(A5,SEQUENCE(LEN(A5)),1),NOT(ISERROR(--MID(A5,SEQUENCE(LEN(A5)),1)))))
Wrapping the ISERROR in NOT reverses the true and false.
in the Excel 2016 function, same thing.
=TEXTJOIN(,,IF(NOT(ISERROR(--MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))),MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1),""))
Awesome!! Thanks for the formulas, M Smith : )
Power query is the best solution, thanks Mr Mike
Thank you brother. You saved me hell of time... atleast 3 days i guess :).. Liked and Subscribed.... :))
You are welcome, brother!!!
Welcome back Mike. Amazing video! I learn a lot from you everyday ! Watching ur old videos as well. They are all amazing as they help me a a lot at work.
Always glad to help, Nader!!!
I am happy to see a new video from you Mike. I hope you are doing well and everything is alright. Thanks a lot. 👍 Salim
I am glad to be back, K D : )
Glad to have you back Mike.
Glad to boomerang back, even if it is at half speed, Fabio!!
You make excel magic, you are the magic. I really inspired by you.
I am so happy to see your video after such a long time. Stay blessed and keep sharing the amazing work.
Glad to be back, Abdul!!!
For dynamic data, my first choice was Power Query until you demonstrated the new Let function. It's great to have options. Flash fill is awesome in the right application. Thanks Mike. Always appreciated.
You are welcome as always, Matt!!!
Amazing video, always fascinated by your PQ skills!!!.
Glad you like it, Teammate cr gr0912!!!
Mike,
Even before I watch Your Video, I can confidently Like the Video. ToOoOoOo GoOoOoOoD!
Glad it is GoOoOoOoOoOoOd for you, Ankur!!!
Hello mike another easy way without formula is by using find & replace (ctrl +H )..first select ranges & find 0 to 9 & replace each find with blank by replace all option.
Power query solution is Amazing .... but i feel sad for the old school ...😉
Sad for the old school...
Good tricks, thanks Mike!👍👍👍
Glad you like it, Luciano!!!
Welcome back I missed your genius videos
Glad to boomerang back and make videos for the Team : )
Mike you are scientist!!
Woah Mike; Brill ! I need to play with this solution, I have soooo many ideas for it 😉
Playing in Excel is good!!!
Once again, you saved the day!!
Hi, I love your tricks with Lists in Power Query.
Yes, lists in PQ are love-worthy : )
Thanks for this wonderful tutorial... Power Query 👍
Yes: Power Query Thumbs Up : )
Good to see you back Mike...
Glad to boomerang back, J DO!!!
This video is brilliant. 🌟 🌟
Thanks..
Thanks for the double comments, K D : ) : )
Boom!POWER QUERY Rocks!!Been Really Impressed With The LET Function What A Great Addition,Still Love FLASH FILL Where Possible..Wicked Tutorial Thank You Mike :-)
You are welcome for the wicked Boom Fun Video, darryl : )
This was incredible!!!! What would you do if you have multiple words (i.e spaces) and also want to remove non-letter symbols?
This is indeed Magic. Thank you.
Thanksssss mike glad for your coming back :-)
Power Query is still the champ. I don't think I've seen a transformation that it can't do.
Yes, Power Query seems even more infinite than Excel Worksheet formulas : )
Learned something before 6am on a Monday. Off to a good start...
Yes!!! Good Start Monday : )
I salute you, sir. Well done
I hope MS would give more support to formulas in Power Query similar to the support we get in the regular Functions
Wonderful Mike,
Thanks
Glad you like it, Sevag!!!
Sir, welcome back, thank you for this trick.
You are welcome, Amador!!!
Request you to make a complete course on Excel 2019
Hi Mike you can use SUBSTITUTE for old school formulae i.e. =SUBSTITUTE(SUBSTITUTE(A5,"1",""),"2","") etc for each of the numbers to replace with ""
LOVE it!!!!!
ExcelIsFun, Mr. Mike, what about bit more complex problem, Let say i do have mixed invoice numbers with text. and I want to keep text with decimal separator (comma or dot) and decimal number?
Can you make a video about it?
Hi Mike.. love this.. the Office 365 formula is definitely cool or cooly or COOL or COOLY :)) Like the PQ solution too.. Text.Remove().. thanks for the good learning there. As always.. Thumbs Up for your thought provoking and challenging problems and solutions! Super Sunday fun at ExcelIsFun!! PS - Hope MS adds normal F9 evaluation capability to LET.
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!!
It is deffinately COOL :). Especially PQ solution! Love it!!! I wish it would be such a worksheet function... :) ♥
Me too. Me Too. Maybe someday Microsoft will allow Power Query & DAX functions in the worksheet?
Very cool, Mike. And thanks for more of the let() function :-} Isn't flash-fill amazing at times!
Yes!!! LET there be fun : )
You are so clever!
Alwasy glad to help with cleverness, Hoi Tin Wan!!!
Thanks mike. At last.....yr back.
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??
Thanks Mike.
You are welcome log time WRH : ) : ) : ) : )
That was really awesome Mike, thank you! Well done.
You are welcome, Chris : ) : )
Thanks Mike :)
Thank you so much for your Tutorial. I have a question about the PMT, RATE in Excel. May I email you to discuss?
COOLY! Old school is good to know, so that we do appreciate the easiness brought by Flash Fill / Power Query.
So solve this problem, PQ should be the preferred way! :) Isn't it?
Hi Mike Sir, what if one wants to retain the numbers instead of text ?
Wel Come Sirji...
Hope All is Well Now..
Glad you like it, Mahesh !
Thanks a lot Mike. Recently I am using below VBA code separate number from text through multiple looping. But i think PQ is just next level.
Sub For_Next_Loop_in_Text()
Dim i As Long 'for looping inside each cell
Dim myValue As String
Dim NumFound As Long
Dim TxtFound As String
Dim r As Long 'for looping through rows
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = StartRow To LastRow
myValue = Range("A" & r).Value
For i = 1 To VBA.Len(myValue)
If IsNumeric(VBA.Mid(myValue, i, 1)) Then
NumFound = NumFound & Mid(myValue, i, 1)
ElseIf Not IsNumeric(Mid(myValue, i, 1)) Then
TxtFound = TxtFound & Mid(myValue, i, 1)
End If
Next i
Range("H" & r).Value = TxtFound
Range("I" & r).Value = NumFound
NumFound = 0
TxtFound = ""
Next r
End Sub
Sub Clear_Values_For_Text_Loop()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("H" & StartRow, "I" & LastRow).ClearContents
End Sub
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 : )
FILTER function rules!
FILTER does rule : )
Thanks
You are welcome, anand!!!
Amazing! thanks a lot
You are welcome a lot, Maan!!!
Fantastic video 👏
Glad you like it, Sachin!!!
Hi, i am following you.. i want a video on excel which will allocate leads to employees evenly among the team. Condition is that leads of same location to be employees of same location by excel formulas.
Welcome mike !!!
Cool, nitesh!!!!
@@excelisfun ❤
As usual , amazed :)
Glad to amaze, Rash !!!
Controversial use of LET there Mike! Putting it within another function. That's crazy. I'd never do that. You're wild. I'd always put it as the final step in the function. In other words, create a variable for your unconcatenated list and then concat that. Also, it makes debugging easier as you can switch your output between your variables to check each step.
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?
I used substitute function 10 times to eliminate all the numbers like
Substitute (Substitute(A1,1,""),2,"")... and so till 0..
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.
My Teacher is BACK !!! WELCOME WELCOME WELCOME huuuhuuuu :) ..
Glad to be back, Apples Oranges!!!!
So if we let the array fall in different cell and if we use concatenate function in the last cell and use concatenate(range) and press F9 in the range then it will hard code the resultant values of cell in {"a","b"} and then remove the {}.
I am sorry but I do not understand your question : (
Hello master, please I have a concern how to split a column that contains dates and amounts into two separate columns (dates and amounts) with power query.
This is Owesome.
Glad it is Owesome for you, Nitin!!!
Thank you much sir
You are welcome so much, Rahul!!
and also is there really no way to make the check box bigger doesn't matter what size of text i use the check box itself is so tiny compared to the columns
is it possible to make the row skip 2 numbers so i can have a locked row 2 with all my highlight names. and row 1 to have the columns in where i can search after the names in that row. eksl i want it to say row 1 and 2 the one to the left not column A but the row numbers so Row 3 will be row 1 and first to call them like 0 and 01
Power Query 1st Rank 2nd flashfill
1st and 2nd!!!! Yes : )
date format typed in dd.mm.yyyy fromat in sap gives decimals figure in excel , how to convert the original numbers(date)
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 : )
Hey Mike. I have a variation on this video which I'd like to see a solution for...Suppose I have number e.g. "72816493" and I want to show each individual numeral in a separate column - what formula will do this? i.e. 72816493 in cell A1 will be broken up into numerals 7 in cell B1, 2 in C1, 8 in D1, 1 in E1, etc.
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.
Hi Mike, I found another way to extract only numbers or only text.
To extract numbers I input =TEXTJOIN("";TRUE;IFERROR(MID(A5;ROW(INDIRECT("1:"&LEN(A5)));1)*1;""))+0
To extract text I input =TEXTJOIN("";TRUE;IF(ISERROR(MID(A5;ROW(INDIRECT("1:"&LEN(A5)));1)*1);MID(A5;ROW(INDIRECT("1:"&LEN(A5)));1);""))
If I had to extract the capital letters from a text containing no numbers at all I input
=TEXTJOIN("";TRUE;IF(EXACT(UPPER(MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1));MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1));MID(A1;ROW(INDIRECT("1:"&LEN(A1)));1);""))
I believe will be helpful for many. Bye.
Very nice, Lampros!!!
Thanks for Sharing, Lampros!
I've become lazy ever since I learned about the Power Tools. I'd use PQ (Power Query) and call it a day.
It looks like a clean up operation, anyway. :-)
Me, too. ;-)
@@GeertDelmulle Double funny : )
Nice but that is a seriously mad number of nested functions at 8:30
Yes, better to use SUBSTITUTE 10 times...
Now it's getting mixed up learning Excel Spill Functions, Power Query (M), DAX, T-SQL ... :( :(
Wouldn’t a substitute function with an array work?
I do not know how to use an array in SUBSTITUTE, But 10 Nested SUBSTITUTES will work : )
1
1st Place trophy goes to you!!!
wow swag
Thanks for your swag comment, Kokneiy : )
Wow mike welcome back ......monday is going good with ur priceless knowledgeable shared video thanks Mike
And plzz check ur inbox i sent you one query,whenever u have time kindly go through and reply with me solution.
👌👌👌👌👌💐💐
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
Cutting edge
Sharp Excel Fun, Jonathan : )
Hi Mike, here we go
=SUBSTITUTE(CONCAT(MID(0&C4,SMALL(ISTEXT(IFERROR(--MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1),"#"))*ROW(INDIRECT("1:"&LEN(C4))),ROW(INDIRECT("1:"&LEN(C4))))+1,1)),0,"")
Inspired from your videos . 🙏🏿🙏🏿🙏🏿 Thank you