I have a column of filenames that I want to remove the file extension from so that I can compare them to another list.
Certain filenames appear like dates when the file extension is removed.
The 'Replace' function literally converts those names into dates.
With Columns(TargetCol) 'Remove filename extensions
.Replace What:=".pdf", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Example filenames that convert to a date
11-6285.pdf results in Nov-85 instead of 11-6285
12-9958.pdf results in Dec-85 instead of 12-9958
Is there a way to remove the file extension without changing the remaining text to a Date datatype?
It doesn't matter if the column is formatted to 'Text' or not.