How to retrieve part of a filename?

I am opening several excel files in a folder and then compare the said file on another file on a different folder. So i thought i could do it like this since those 2 files has almost the same filename only differs on date.

Ex.
Folder1
Tokyo_201204_Generic.xls <----this is always the format of the filenames City Name _ Date _ Generic
Hiroshima_201204_Generic.xls

Folder2
Tokyo_201205_Generic.xls
Hiroshima_201205_Generic.xls

So if I open [Tokyo_201204_Generic.xls] I need to compare it to [Tokyo_201205_Generic.xls]. To be able to do it I need to get "Tokyo_" and compare if there is a file on folder2 with filename that starts with "Tokyo_". I can't include the date to the comparison since the date varies every month. Folder1 and Folder2 paths are user inputs. How do I do this with macro?

Any help would be greatly appreciated.

Hi,

You can use mid function

dim test as string
test = your file name

if ((mid(test,5))= "Tokyo") then 'this 5 will vary as per lenght of yor city name
msgbox("matchfound")
else
msgbox("not found")
end if

I hope this will help you

Thank you gaurisneha but I think it won't work that way.
Each folder contains 100+ files and City Names have different lengths
is there a way that you can make a macro search for an "_" and then
copy the characters before it. that way I can get the city name...I think

how's this?

Public Function MatchCity(City1 As String, City2 As String) As Boolean

If UCase(Left(City1, InStr(1, City1, "_") - 1)) = UCase(Left(City2, InStr(1, City2, "_") - 1)) Then
    MatchCity = True
End If

End Function

Sub testingMatch()
MsgBox MatchCity("Tokyo_123", "ToKyo_234")
End Sub

Thank you very much,...works fine..

Glad it works thanks for the feedback, here's another idea, little shorter:

Public Function MatchCity2(City1 As String, City2 As String) As Boolean

If UCase(Split(City1, "_")(0)) = UCase(Split(City2, "_")(0)) Then
    MatchCity2 = True
End If

End Function