Consulting

Results 1 to 10 of 10

Thread: I see there's a new function in Excel

  1. #1
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location

    I see there's a new function in Excel

    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?
    Last edited by Aussiebear; 05-24-2024 at 03:16 AM. Reason: the original construct didn't sound okay.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    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
    Be as you wish to seem

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Are you in one of those really motivated moods right now?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,743
    Location
    @AB -- interesting
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    @Paul, I can foresee a multitude of uses. Hopefully others can post examples of how they think it can be used.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    Quote Originally Posted by Aussiebear View Post
    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.
    Be as you wish to seem

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,743
    Location
    Quote Originally Posted by Aflatoon View Post
    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?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    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.
    Be as you wish to seem

  9. #9
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    because we don't know what we are searching for......
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Quote Originally Posted by Aflatoon View Post
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •