Is there any formula can read the data from the file name and mark it onto an excel file ?

Every month, I need to scan thousands of testing reports into PDF format.

The File naming system is : XXX ZZZZZ 2023-MM-DD.pdf

XXX : station code (3 digits)
ZZZZZ : Machine Number (from 2 digits to 5 digits)

ALL pdf files will store in a directory
C:\2023-01\XXX ZZZ 2023-MM-DD.pdf


For example :
I created 5 different PDF files

CEN EAP1 2023-01-10.PDF
CIO L2 2023-01-24.PDF
JOR BL1 2023-01-07.PDF
LIV L1 2023-01-21.PDF
LON L1 2023-01-18.PDF


My question is :
Is there any excel formula be able to read from the above pdf file names and automatically mark "PM" in the attached excel file ?


Or, is there any other intelligent ways to do the same ?

wow ... that's awesome
thank you so much for that

@protonLeah

your VBA helped me a lot, very appreicated

Just a bit more request, is it possible to generate a date and put it onto sheet "PM date" ?
Please refer to a new attachment

As this is requested by our client.

wow . . . that's great !!!
thank you so much

@protonLeah

Can you please take a look at the excel file again ?
The result generated at the sheet "PM Date" seems have problems.

Sorry for causing you troubles.

Hello Proton,

Thanks for your hard work.
some errors during debugging, I am studying right now.