I see there's a new function in Excel

[Aussiebear]

Excel 365 beta channel has given us a look at three new functions "Regextest, Regexreplace, & Regexextract". As I understand it they are as follows:

1. Regextest. Enables the User to test the validity of a String based on the pattern.

Example. You require a User to enter a correct phrase in cell D14. In another cell say E14 you could write


=RegexTest(D14, "Hello World", "world")

Would result in "True" whereas "Whirled" would result in "False" because the pattern is incorrect.

2. Regexextract. Enables the User to extract a substring from within a string.

Example. You need to extract the email address from a larger string. In the string in cell C4 "You may contact us at User@vbaexpress.com for further information". You could use

=RegexExtract(C4,"[\w.-] +@[\w.-]+\.\w+$")

Which should result in User@vbaexpress.com

3. RegexReplace. Enables the User to replace data with a mask (in an effort to say hide confidential data)

Example. Just say you had a part number that you wish to have it remain confidential for what ever reason. This part number consisted of a 9 digit string resides in cell A5. You could construct your function like this


=Regexreplace(A5,"\d{3}-\d{3}", "xxx-xxx")


This results in the first 6 digits being replaced by x.

Are there other usages that anyone could think of?

[Aflatoon]

I can count on one hand the number of times I've wanted RegEx in Excel. But I suppose at least now it will still be available when the vbscript.dll disappears. And MS gets to push AI even harder because nobody wants to try and figure out complicated regex patterns on their own ;)

[Aussiebear]

Are you in one of those really motivated moods right now?

[Paul_Hossler]

@AB -- interesting

[Aussiebear]

@Paul, I can foresee a multitude of uses. Hopefully others can post examples of how they think it can be used.

[Aflatoon]

Are you in one of those really motivated moods right now?

Maybe. :) And maybe I've already seen too many "woohoo" posts about this from MVPs, when I suspect 99.8% of Excel users will never use these functions. Trying to explain character classes, greediness vs laziness, back references and subgroups to your average office worker is not something I look forward to. It'll be even worse than the time I tried to explain Xpath usage in FILTERXML... ;)

Maybe I've been on forums too long - I think I'm starting to sound like Bob on a particularly grumpy day.

[Paul_Hossler]

Maybe. :) And maybe I've already seen too many "woohoo" posts about this from MVPs, when I suspect 99.8% of Excel users will never use these functions. Trying to explain character classes, greediness vs laziness, back references and subgroups to your average office worker is not something I look forward to. It'll be even worse than the time I tried to explain Xpath usage in FILTERXML... ;)

Maybe I've been on forums too long - I think I'm starting to sound like Bob on a particularly grumpy day.


FWIW, I agree. WS formulas like this are impressive and done by someone who really knows their stuff, but are practially unmaintainable by the average person (like me who can never get all the )'s in the right places)


=LET(HeaderMatch1,MATCH(E1,StockTable___2[#Headers],0),
HeaderMatch2,MATCH(E2,StockTable___2[#Headers],0),
ForHeaders,TOROW(MATCH(D5:K5,StockTable___2[#Headers],0),2),
Filt1,CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH("*"&G1&"*",INDEX(StockTable___2,,HeaderMatch1))))+(ISNUMBER(SEARCH("*"&G2&"*",INDEX(StockTable___2,,HeaderMatch1))))),ForHeaders),
Filt2,CHOOSECOLS(FILTER(StockTable___2,(ISNUMBER(SEARCH("*"&G1&"*",INDEX(StockTable___2,,HeaderMatch2))))+(ISNUMBER(SEARCH("*"&G2&"*",INDEX(StockTable___2,,HeaderMatch2))))),ForHeaders),
IF(COUNTA(E1:E2)=0,"No Search Columns",IF(AND(E1<>"",E2=""),Filt1,IF(AND(E2<>"",E1=""),Filt2,VSTACK(Filt1,Filt2)))))

I wonder if they work with dynamic array functions?

[Aflatoon]

I'd imagine they will as they're just text functions.

As an aside, I'll never understand why people use asterisk wildcards round a single term in SEARCH. It serves no purpose. :)

[Aussiebear]

because we don't know what we are searching for...... :slingshot

[Aussiebear]

Maybe I've been on forums too long - I think I'm starting to sound like Bob on a particularly grumpy day.

If you sound like Bob, you are definitely heading in the right direction..... based on me being the director of communications at my company. :jail: