Hi - Dave here.
Happy Friday!
Big news...Microsoft just added regex support to Excel!
After decades of clunky workarounds and terribly complicated formulas, you can now use three powerful new functions - REGEXTEST, REGEXREPLACE, and REGEXEXTRACT - to handle the trickiest text operations.
For example, the workbook below shows the "before" formula used to extract the number from each product code. The full formula in D5 looks like this:
=TEXTJOIN("",TRUE,IFERROR(MID(B5,SEQUENCE(LEN(B5)),1)+0,""))
The "after" formula, based on REGEXETRACT, looks like this:
=REGEXEXTRACT(B5,"d+")
I think you'll agree the second formula is simpler :)
[
Download the workbook and read the full article]
Read the complete article and download the workbook at the link above. I show each function in action, provide some background on regex in Excel, and include a mini cheat sheet of simple patterns.
Note: These new regex functions are only available in Excel 365.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I write about Excel formulas. You can sign up on our home page.