Extract specific text from cell to save file

Hi all,

I get a CSV file which I format and want to save as an Excel file.

In Cell A3 is always "Period : 02/06/2008 - 09/02/2008 " (Note final space as well)

I have been messing with code to extract various bits from the above as I want to save the file as:

Path & 2008 Jun 02 - 09.xls)

Sub Extract_Date()
Dim dtStartDay As Variant
Dim dtEndDay As Variant
Dim dtMonYear As Variant
Dim dtMon As Variant
Dim dtYear As Variant
Dim dtFullDate As Variant
Dim sFile As Variant

dtFullDate = Right(Range("A3"), 11)
dtMonYear = Right(dtFullDate, 8)
dtMon = Left(dtMonYear, 2)
dtYear = Right(dtFullDate, 5)
dtEndDay = Left(dtFullDate, 2)
strMon = MonthName(dtMon)
sFile = dtYear & " - " & strMon & " - " & dtEndDay & ".xls"
End Sub

Firstly can anyone tell me how to get the dtStartDay out of that cell and secondly am I going about this whole thing the right way or is there an easier (more elegant) way to achieve this.

Many thanks as always

Seamus

Hi Seamus,

I'm puzzled as to where you get the text 'Jun' from.

Was that a typo in the second date element and you meant '09/06/2008' not '09/02/2008'?

i.e. the month (06=June) is indeed the central characters in the two date elements.

Rgds

Hi Richard,

Yes it was a typo - I meant it to say:

"Period : 02/06/2008 - 09/06/2008 "

Any help would be appreciated

Thanks

Seamus

Hi,

Assuming cell B3 is blank and which can be used to store the full Path/File Name string, then one way is the following

Sub ExtractDate()
    Dim stFileName As String, stSaveName As String
    Range("B3") = "=MID(A3,LEN(A3)-4,4)&"" ""&TEXT(DATEVALUE(MID(A1,10,10)),""mmm"")&"" ""&MID(A1,10,2)&"" - ""&MID(A3,23,2)&"".xls"""
    stFileName = Range("B3")
    stSaveName = Application.DefaultFilePath & stFileName
End Sub

HTH

Hi Richard

Fantastic - thanks very much.

I just had to tweak one line to give me the exact desired answer.

Your line of

Range("B3") = "=MID(A3,LEN(A3)-4,4)&"" ""&TEXT(DATEVALUE(MID(A1,10,10)),""mmm"")&"" ""&MID(A1,10,2)&"" - ""&MID(A3,23,2)&"".xls"""

gave me a #VALUE error so I changed it to

Range("B3") = "=MID(A3,LEN(A3)-5,4)&"" ""&TEXT(DATEVALUE(MID(A3,10,10)),""mmm"")&"" ""&MID(A3,10,2)&"" - ""&MID(A3,23,2)&"".xls"""

and that was perfect.

Thanks again

Seamus

Thanks for the feedback Seamus.

Yes that was an obvious typo of mine - well spotted.

Regards