Hi - Dave here.
Happy Friday!
How can you extract a date like "6/17/24" from a text string?
A classic way is to use a formula based on the MID and SEARCH functions. You can see this approach below, where the formula in cell D5 is:
=MID(B5,SEARCH("??/??/??",B5),8)+0
[
Download the workbook and read the full explanation]
This works pretty well, but the formula is not very robust because it will also match strings like "AA/BB/CC" or even "AAAA/BB/CCCC". The problem is that Excel wildcards are quite primitive, so there is no simple way to match just numbers with word boundaries.
I mentioned last week that a new regex function, REGEXTEST, was just released to the Beta channel in Excel 365. There are actually 3 new regex functions that were introduced, and one of them, REGEXEXTRACT, can solve this problem nicely like this:
This is a far better formula for extracting dates because it only matches numbers, and it includes word boundaries: It matches 1-2 digits for the month, 1-2 digits for the day, and 2-4 digits for the year. Of course, this is regex, and regex takes practice :) The good news is that REGEXEXTRACT is a
huge upgrade to Excel's tools for matching and extracting text. Read the article for all the details and to download the workbook.
Note: REGEXEXTRACT is only available in the Beta channel of Excel 365 for now. The MID + SEARCH formula will work in any version.
Excel formulas
We maintain a list of over 500 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 take a detailed look at one useful Excel formula. You can sign up on our home page.