asked on

Excel VBA function 'REPLACE' turns text into a Date.

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.

VBA syntax:
With Columns(TargetCol) 'Remove filename extensions
.Replace What:=".pdf", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With

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.

David Sankovsky

If the extensions are ALWAYS PDF, you can use the a simple inline command instead of going VBA script on it.
For example "=LEFT(A2,FIND(".pdf",A2,1)-1)"
Also, if it may have several types, but the filename certainly has no dots in it, you can simple search for "." as in "=LEFT(A2,FIND(".",A2,1)-1)"

See attached file for example.

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeff Geiselman

ASKER

Thanks, this should work.
It at least retains the correct format & datatype.

One minor issue:
'Loop While Not c Is Nothing And c.Address <> firstAddress'
causes the error 'Object variable or With Block Variable not set' when 'c. is Nothing'.
May just need to trap for 'firstAddress' without referencing a null 'c'.

slubek

When you use General format, Excel tries to recognize cell values . Thats why it changes result of Replace into date.
Change your column format to String (Text) to prevent this.
Or maybe you should disable ""Extend data range formats and formulas." option (File>Options>Advanced)

Rgonzo1971

then try

With Columns("A:A")
    Set c = .Find(".pdf", LookAt:=xlPart)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.NumberFormat = "@"
            c.Value2 = Left(c.Value, Len(c.Value) - 4)
            Set c = .FindNext(c)
            On Error Resume Next
            bAddress = c.Address <> firstAddress
            On Error GoTo 0
        Loop While Not c Is Nothing And bAddress
    End If
End With

Open in new window

Regards

Jeff Geiselman

ASKER

slubek,,
In my Excel, it doesn't matter whether the column is formatted ahead of time to String(Text) or not, when the VB 'Replace' function executes, it actually changes the format to 'Date'.
I have even tried the TextToColumn 'Text' datatype with the same results.

aikimark

I think you're going to need to a new column for a formula that removes the file extension.

=SUBSTITUTE(A1,".pdf","")

Open in new window

slubek

Sorry - my fault.
Replace works properly when your cell content begins with ', for example:
'11-6285.pdf
'12-9958.pdf
etc.
Add a column with concatenation formula, like: ="" & A1, copy it and paste "special as values". You will get column with values beginning with apostrophes. You can use your Replace on that column.