Forum Discussion
Extract mm.yyyy from text
Hello,
I have a column with text with different length, which contains month and year in a format mm.yyyy and it could be located at a different place within the cell. How can I extract the mm.yyyy only?
Thanks a lot in advance.
=REGEXEXTRACT(A1, "\d{2}\.\d{4}")
20 Replies
Hello SDj1
Believe it or not, extracting from +/- inconsistent strings is a quite common request on forums
If one of the proposals you got solves your issue it would help people who Search for existing solution that you mark the corresponding post (Mark as solution at the bottom of each reply you get here)
Thanks & no rush
Hi Lorenzo
Thanks for navigating me trough this exercise. The LAMBDA solution that you provided below is too complex for me and I couldn't use it. What was helpful was the =REGEXEXTRACT(A1, "\d{2}\.\d{4}"), where I used "." or "_" to extract some of the data. For the months listed with their names, I used filter for each and separate month and then I copied the mm.yyyy manually. The problem with my database was that the dates appeared not only after "dated" but after/before different other words in various sentences.
Hi SDj1
Obviously your samples didn't reflect all the variations that exist in your DB - never easy
I hope you sorted it out finally. Otherwise & if your DB isn't top secret feel free to share it (i.e. with OneDrive, Google Drive or the like) and I'll see if there a way to do something without manual operations
Cheers
Lz.
I used the following formula in cell C2 (see the screenshot above):
=--SUBSTITUTE(TEXTBEFORE(TEXTAFTER(A2:A11, "dated "), ".pdf"), ".", "/")I have formatted the dates in column C as mm.yyyy but you can choose the format you want.
If you want the dates to appear as text, use this formula:
=TEXT(SUBSTITUTE(TEXTBEFORE(TEXTAFTER(A2:A11, "dated "), ".pdf"), ".", "/"), "mm.yyyy")Hope this helps.
IlirU
Hi,
I used this formula in cell C2:
=--SUBSTITUTE(TEXTBEFORE(TEXTAFTER(A2:A11, "dated "), ".pdf"), ".", "/")I have formatted the cells in column C as dates, so mm.yyyy
If you want your data to appear as text then use this formula:
=TEXT(SUBSTITUTE(TEXTBEFORE(TEXTAFTER(A2:A11, "dated "), ".pdf"), ".", "/"), "mm.yyyy")Hope this helps.
IlirU
Hello SDj1,
You can extract mm.yyyy from text in Excel in several ways, depending on your version:
- SEARCH + MID (works in all versions) =MID(A1, SEARCH("??.????", A1), 7)
- REGEXEXTRACT (Excel 365 only) =REGEXEXTRACT(A1, "\d{2}\.\d{4}")
- TEXTJOIN + MID + ROW (array formula) =TEXTJOIN("",,IF(ISNUMBER(--MID(A1,ROW($1:$50),7)),MID(A1,ROW($1:$50),7),""))
- TEXTBEFORE / TEXTAFTER (Excel 365 only) =TEXTBEFORE(TEXTAFTER(A1," "), " ")
- Power Query Load your column into Power Query → use Extract → Text Between Delimiters with “.” as delimiter → recombine left and right parts to form mm.yyyy..
- VBA Regex (works in all versions, requires macros) Function ExtractDate(txt As String) As String Dim RE As Object, matches As Object Set RE = CreateObject("VBScript.RegExp") RE.Pattern = "\b\d{2}\.\d{4}\b" RE.Global = True If RE.Test(txt) Then Set matches = RE.Execute(txt) ExtractDate = matches(0) Else ExtractDate = "" End If End Function
Then use =ExtractDate(A1) in your sheet.
Official Microsoft documentation for all these functions is available here: https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
For the cells where the month is written out as text (like “12 December 2024” or “19 June 2025”), you can normalize them in Excel 365 by extracting the part after “dated” and converting it into a proper date:=DATEVALUE(TEXTAFTER(A1,"dated "))
This works even if the date is in the middle of the text. Once Excel recognizes it as a date, apply a custom number format:mm.yyyy
That way, whether the source was 12.2024, 31.03.2025, or 19 June 2025, Excel will display it consistently as 12.2024, 03.2025, 06.2025, etc.
=REGEXEXTRACT(A1, "\d{2}\.\d{4}")
invoice 2-10747 dated 12.2024.pdf
invoice 2-10846 dated 04.10.2024.pdf
invoice 2-11154 dated 12 December 2024.pdf
invoice 2-11196 dated 16 December 2024.pdf
invoice 2-11339 dated 29 January 2025.pdf
invoice 2-11427 dated 21 February 2025.pdf
dated 31.03.2025 invoice 2-11559.pdf
invoice 2-11567 dated 08 April 2025.pdf
dated 13 May 2025 invoice 2-11706.pdf
invoice 2-11928 dated 19 June 2025.pdf
Excel 365 for Winfows
You can extract **mm.yyyy** from text using a few different approaches, depending on your Excel version.
Excel (Microsoft 365 / Excel 2021+) – Best Method
Use TEXTSPLIT + FILTER with a pattern match:
=TEXTBEFORE(TEXTAFTER(A1,"."),".")❌ *This won’t reliably work when the position varies.*
Recommended (Robust) Formula
This works no matter where mm.yyyy appears in the text:
=TEXTJOIN("",,MID(A1,SEQUENCE(LEN(A1)),1))❌ *Still not precise for pattern matching.*
Correct & Reliable Solution (All Excel Versions)
Using MID + SEARCH
=MID(A1,SEARCH(".",A1)-2,7)
If you want, tell me your Excel version and I’ll tailor the best formula for you.Hi joshaali
Thank you. My version is Excel 365. I have a very huge set of data and I have to extract the dates from it in the format mm.yyyy so I can produce some reports and analyses. I have the dates shown at different places in the cells, some of them are with the month with its name, some have dots as a separator, some have an interval. It is a wild mix. Here is an example:
invoice 2-10747 dated 12.2024.pdf
invoice 2-10846 dated 04.10.2024.pdf
invoice 2-11154 dated 12 December 2024.pdf
invoice 2-11196 dated 16 December 2024.pdf
invoice 2-11339 dated 29 January 2025.pdf
invoice 2-11427 dated 21 February 2025.pdf
dated 31.03.2025 invoice 2-11559.pdf
invoice 2-11567 dated 08 April 2025.pdf
dated 13 May 2025 invoice 2-11706.pdf
invoice 2-11928 dated 19 June 2025.pdf
REGEXEXTRACT() or FILTERXML() may help. Post few of a sample data.
Hi
For this kind of things you should share - say 10 to 20 representative & different - of your actual text values + the version of Excel you run & on which platform (Windows, Mac, Web...)Hi
This is the sample of the text in the various cells from which I would like to extract the date in MM.YYYY format:
invoice 2-10747 dated 12.2024.pdf
invoice 2-10846 dated 04.10.2024.pdf
invoice 2-11154 dated 12 December 2024.pdf
invoice 2-11196 dated 16 December 2024.pdf
invoice 2-11339 dated 29 January 2025.pdf
invoice 2-11427 dated 21 February 2025.pdf
dated 31.03.2025 invoice 2-11559.pdf
invoice 2-11567 dated 08 April 2025.pdf
dated 13 May 2025 invoice 2-11706.pdf
invoice 2-11928 dated 19 June 2025.pdf
Excel 365 for Windows
Hi SDj1
Your examples were definitively required as your pseudo-dates are not always in mm.yyyy format as you initially said. Anyway, with your example in B2:B21 below (pic. cropped)
in D2:
=LET( afterDated, TEXTAFTER( B2:B21, "dated " ), beforePdf, TEXTBEFORE( afterDated, ".pdf" ), TRIM( TEXTBEFORE( beforePdf, " invoice ",,,, beforePdf ) ) )Attaching a sample file & crossing fingers it won't be dropped by this site...
EDIT A variant that replaces months in MMMM format with their corresponding number:
With a LAMBDA named EXTRACT_DATE stored in the Name Manager, in F2:
=EXTRACT_DATE( B2:B21 )where EXTRACT_DATE is:
=LAMBDA( reference, LET( seq, SEQUENCE( 12 ), months, TEXT( DATE( 2000, seq, 1 ), " MMMM " ), replacer, IF( seq < 10, ".0" & seq, "." & seq ) & ".", EXTRACT, LAMBDA( rw, LET( searchMonths, IFERROR( SIGN( SEARCH( months, rw ) ), 0 ), monthsAsMM, IF( SUM( searchMonths ) = 0, rw, SUBSTITUTE( rw, XLOOKUP( 1, searchMonths, months ), XLOOKUP( 1, searchMonths, replacer ) ) ), TEXTBEFORE( TEXTAFTER( monthsAsMM, "dated " ), {".pdf"," invoice "} ) ) ), BYROW( reference, EXTRACT ) ) )
Thank you and sorry.
These are some of the texts in the column:
invoice 2-10747 dated 12.2024.pdf invoice 2-10846 dated 04.10.2024.pdf invoice 2-11154 dated 12 December 2024.pdf invoice 2-11196 dated 16 December 2024.pdf invoice 2-11339 dated 29 January 2025.pdf invoice 2-11427 dated 21 February 2025.pdf invoice 2-11559 dated 31.03.2025.pdf invoice 2-11567 dated 08 April 2025.pdf invoice 2-11706 dated 13 May 2025.pdf invoice 2-11928 dated 19 June 2025.pdf The problem is with the cells where the Month is shown as a name of the month instead as 03.2024, for example. This is Excel 365 for Windows. And in some cells the date is in the middle of the cell and not in the end.
