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?

Please Login or Register  to view this content.

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

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

Please Login or Register  to view this content.