Aussiebear
05-24-2024, 03:12 AM
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?
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?