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.
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
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.
@@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.
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
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.
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.
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
@@ExcelMoments 🙌
That’s impressive! Especially the Regex formula in Google Sheets!
Now we have it in Excel(Beta channel), so we are good to go 😀
Some nice solutions Victor, thank you for another master class
Thanks Steve
Thank you so much ❤️
You are welcome
Excellent
Thanks
these solutions are fabulous :) mine was very clunky :)
Hahahaha....shortest is not nwcesaarily best 😀
Ohhh. Much easier in Google Sheets. Microsoft should take good note. Thanks for the tutorial Victor.
Now, as easy in Excel, with REGEX in beta! 😀
Thanks Victor Google seems to easy
bsolutely. But with the announcement yesterday, that function is now in Excel's Beta channel. So, Excel is not behind anymore 😁😁😁😁
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.
2 REDUCE functions! Just wow. Thanks for the kind comments
@@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.
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.
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
@@ExcelMoments Hhhhhhmmmmm !!!!
Let's make the [match_mode] of TEXTSPLIT() case_insensitive
CONCAT(TEXTSPLIT(A2,BASE(SEQUENCE(36,,0),36),,1,1))
@@oyekunlesopeju9312 Great. So that's not because of the BASE function 😁😁 I already figured this was where you were headed.
@@ExcelMoments You're right !!! 👏
Not because of the BASE() function
MAY 20, 2024
New Regular expression (Regex) functions in Excel
I don't have it yet!
Please what is the real life usage?
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.
Data Cleansing !!!