Hi Pam -

Here is another version that selects the date from the filename as the first
6 consecutive numeric characters. It can therefore tolerate filenames that
don't conform to your example as well as extra, non-date numeric characters.
It also provides a conversion from the date substring to an excel date value.

Sub Pam()

fname = "MY Sheet_070331_US.xls"

For i = 1 To InStr(fname, ".xls") - 1
digit = Mid(fname, i, 1)
If IsNumeric(digit) Then
If IsEmpty(sDate) Then
sDate = digit
digitCount = digitCount + 1
lastDigitPosition = i
Else
If i - lastDigitPosition = 1 Then
sDate = sDate & digit
digitCount = digitCount + 1
If digitCount = 6 Then Exit For
End If
lastDigitPosition = i
Else
sDate = Empty
digitCount = 0
End If
End If
Else
lastDigitPosition = i
sDate = emtpy
digitCount = 0
End If
Next i

MsgBox "The date string is: " & sDate & Chr(13) & Chr(13) & _
"The converted date is: " & Format(DateSerial(Val(Left(sDate, 2)), _
Val(Mid(sDate, 3, 2)), Val(Right(sDate, 2))), "yy/mm/dd")

End Sub
--
Jay


"Pam" wrote:

Hi

I was wondering if anyone new exactly how to use the chop function
to chop up a string. Basically what I am trying to do is chop up thre
namer of
a file which contains the filename with date. I want to get the date
for that file
name; Here is an example

MY Sheet_070327_US.xls

I want to chop it up so I just end up with the date.



Your help is appreicated. Thanking you in advance


Pam