Forum Discussion

SDj1's avatar
SDj1
Copper Contributor
Jan 21, 2026

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 ...
excel
SDj1's avatar
SDj1
Copper Contributor
Jan 21, 2026

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

Lorenzo's avatar
Lorenzo
Silver Contributor
Jan 22, 2026

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 )
  )
)

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 23, 2026

    SDj1​ 

    RegEx by Copilot:

    =TRIM(
  REGEXEXTRACT( B2:B21,
    "(\d{2}\.\d{4})|(\d{2}\.\d{2}\.\d{4})|(\d{1,2}\s+[A-Za-z]+\s+\d{4})"
  )
)

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 22, 2026

    SDj1​ 

    Still with the examples you provided and data in B2:B21:

    =LET(
  ref,            B2:B21,
  invoiceDated,   REGEXREPLACE( ref, "invoice \d{1}\-\d{5} dated ", "" ),
  datedInvoice,   IF( invoiceDated = ref,
                    TEXTBEFORE( TEXTAFTER( ref, "dated " ), " invoice " ),
                    invoiceDated
                  ),
  TRIM( TEXTBEFORE( datedInvoice, ".pdf",,,, datedInvoice ) )
)

     

Resources