Skip to main content
Inspiring
August 29, 2018
Question

Extract date from Excel file string

  • August 29, 2018
  • 2 replies
  • 496 views

I have file names like this

P_PAUL-QR-20180827183000.xls

Where 20180827183000 indicates 08/27/2018 at 6:30PM

L_PAUL-QQ-20180828140000.xls
Where 20180828140000  indicates 08/28/2018 at 2:00PM

How do I extract the 20180827183000 or the 20180828140000 from the file name string?

This topic has been closed for replies.

2 replies

BKBK
Community Expert
Community Expert
August 29, 2018

<cfscript>

filename="P_PAUL-QR-20180827183000.xls";

dateTimeString=listLast(listFirst(filename, "."), "-");

</cfscript>

WolfShade
Legend
August 29, 2018

The portion that you want is always 14 integers in length.  RegEx to the rescue. 

<cfset fileName = {however you are providing it} />

<cfset fnArray = REmatch("\d{14}",fileName) /><!--- returns an array of the matched values --->

<cfswitch expression="#arrayLen(fnArray)#">

    <cfcase value="1"><cfset datetime = fnArray[1] /></cfcase>

    <cfdefaultcase>Either there were zero datetimes, or more than one and I didn't know what to do.</cfdefaultcase>

</cfswitch>

HTH,

^ _ ^