bsolutely. But with the announcement yesterday, that function is now in Excel's Beta channel. So, Excel is not behind anymore 😁😁😁😁
@LeilaGharani4 ай бұрын
Love that Base function! Hadn't seen it before. Just went in a rabbit hole learning what it does and what it's used for. Fascinating! Thanks for sharing.
@ExcelMoments4 ай бұрын
HI leila, it is not one of the commonly used ones. Fortunately, we now have REGEX, which makes this kind of extraction extremely easy, like you have demonstrated in your video
@LeilaGharani4 ай бұрын
@@ExcelMoments 🙌
@westleyempeigne65413 ай бұрын
That’s impressive! Especially the Regex formula in Google Sheets!
@ExcelMoments3 ай бұрын
Now we have it in Excel(Beta channel), so we are good to go 😀
@marektomanek19134 ай бұрын
these solutions are fabulous :) mine was very clunky :)
@ExcelMoments4 ай бұрын
Hahahaha....shortest is not nwcesaarily best 😀
@stevereed57764 ай бұрын
Some nice solutions Victor, thank you for another master class
@ExcelMoments4 ай бұрын
Thanks Steve
@ankurshukla25164 ай бұрын
Thank you so much ❤️
@ExcelMoments4 ай бұрын
You are welcome
@Wozzuup4 ай бұрын
MAY 20, 2024 New Regular expression (Regex) functions in Excel
@ExcelMoments4 ай бұрын
I don't have it yet!
@IvanCortinas_ES4 ай бұрын
Ohhh. Much easier in Google Sheets. Microsoft should take good note. Thanks for the tutorial Victor.
@ExcelMoments4 ай бұрын
Now, as easy in Excel, with REGEX in beta! 😀
@williamarthur48014 ай бұрын
Just to be different ; =BYROW( F4:F7,LAMBDA(Txt, LET( alltext, REDUCE( Txt,SEQUENCE(10,1,0,1), LAMBDA(x,y, SUBSTITUTE( x,y," "))), special, REDUCE( LOWER( alltext ), CHAR(SEQUENCE(26,1,97,1)),LAMBDA(x,y, SUBSTITUTE( x, y, "" ))),special) )) Always look forward to your work, still think your insert blanks brilliant.
@ExcelMoments4 ай бұрын
2 REDUCE functions! Just wow. Thanks for the kind comments
@williamarthur48014 ай бұрын
@@ExcelMoments you can then split the special to get jus the alphabet and a switch to looking t a drop down; LET(ra, SEQUENCE(10,1,0,1), txt, REDUCE( D4,ra,LAMBDA(x,y, SUBSTITUTE(x,y,"") )), numbers, REDUCE( D4, MID( txt,SEQUENCE(LEN(txt)),1),LAMBDA(x,y, SUBSTITUTE( x,y,"") )), special, REDUCE(txt, CHAR( SEQUENCE(26,1,97,1)),LAMBDA(x,y, SUBSTITUTE( LOWER(x ),y,""))), text, REDUCE(txt, MID(special,SEQUENCE(LEN(special)),1),LAMBDA(x,y, SUBSTITUTE( x, y, ""))), SWITCH( G3, "All", D4,"Text",text, "Number", numbers, "Special", special) ) Thank you for the inspiration.
@qadeerhussain53514 ай бұрын
Excellent
@ExcelMoments4 ай бұрын
Thanks
@oyekunlesopeju93124 ай бұрын
With BASE() function you do not need to convert the text to uppercase. We can thus shorten the formula length by removing the UPPER() function.
@ExcelMoments4 ай бұрын
Thanks for your comment. I think you may want to test that again, Your function will only work without the UPPER if all characters in the string are UPPER CASE, but if you have lower case characters, they would be returned alongside the special characters. You can test and revert
@oyekunlesopeju93124 ай бұрын
@@ExcelMoments Hhhhhhmmmmm !!!! Let's make the [match_mode] of TEXTSPLIT() case_insensitive CONCAT(TEXTSPLIT(A2,BASE(SEQUENCE(36,,0),36),,1,1))
@ExcelMoments4 ай бұрын
@@oyekunlesopeju9312 Great. So that's not because of the BASE function 😁😁 I already figured this was where you were headed.
@oyekunlesopeju93124 ай бұрын
@@ExcelMoments You're right !!! 👏 Not because of the BASE() function
@jugubitrus72954 ай бұрын
Please what is the real life usage?
@ExcelMoments4 ай бұрын
I can think of a usage for removing the special characters. There have been times when i copied data from some legacy system to excel and it did come with some special and non-printable characters when pasted. A technique like this can allow you to remove the special characters and retain only proper texts, if I may call them that.