Home I have a list of excel filenames in the column A of my worksheet and I would like to remove the .xls and .xlsm tags from the filenames using VBA. How?

I have a list of excel filenames in the column A of my worksheet and I would like to remove the .xls and .xlsm tags from the filenames using VBA. How?

sat
1#
sat Published in 2017-12-07 18:50:49Z
 I want to remove the .xls and .xlsm tags using the "." in the VBA logic, but I am a beginner in VBA and I am wondering how I can accomplish the objective ?
Maldred
2#
 This should do it... Just pass in the range that you want to extract from Sub RemoveExtension(rng As Range) ' Declare your variables Dim LR As Long Dim i As Long Dim str() As String With rng ' Find the last row LR = .Cells(.Rows.Count, 1).End(xlUp).Row ' Enter loop For i = LR To 1 Step -1 If Not (IsEmpty(.Cells(i))) Then ' Extract and split text using "." as a delimiter str() = Split(.Cells(i).Value, ".") ' Rewrite text in cell from first array variable in str() .Cells(i).Value = str(0) End If Next i End With End Sub  You can call this function with another Sub function... Example below: Sub Macro1() ' Change Range() to what every range you need Call RemoveExtension(Range("A2:A10")) End Sub  Bare in mind this only works on columns, so you cannot pass in Range("A1:C3")
 You need to login account before you can post.
Processed in 0.30512 second(s) , Gzip On .