vba to get file extension from col A file names

[StephCA]

Hi: Excel2003. I have a list of file names in column A (340 rows). I want to
copy the extension (only) from each name in col A to col B -- so I can sort
the worksheet by file extension. There are about 15 extensions (.xls, .doc,
..lnk etc.) Can someone provide me the VBA code, or a starting point, to do
this?

TIA, Stephanie

 

[Rick Rothstein]

Give this macro a try...

Sub GetExtensions()
Dim X As Long
Dim LastRow As Long
Dim FName As String
With Worksheets("Sheet3")
LastRow = Worksheets("Sheet3").Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
FName = .Cells(X, "A").Value
.Cells(X, "B").Value = Mid(FName, InStrRev(FName, ".") + 1)
Next
End With
End Sub

 

[StephCA]

Rick, this was perfect. Thanks! One question: I'll always be running this on
the active worksheet. How can I specify the "active" sheet where you show
"sheet3" in the code. TIA again, Stephanie

 

[Rick Rothstein]

Try this...

Sub GetExtensions()
Dim X As Long
Dim LastRow As Long
Dim FName As String
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
FName = .Cells(X, "A").Value
.Cells(X, "B").Value = Mid(FName, InStrRev(FName, ".") + 1)
Next
End With
End Sub

 

[Rick Rothstein]

For the archives... I left an extra reference to the worksheet inside the
With block (doesn't really hurt anything in the end, but it was
unnecessary). This is the code as it should have been posted...

Sub GetExtensions()
Dim X As Long
Dim LastRow As Long
Dim FName As String
With Worksheets("Sheet3")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
FName = .Cells(X, "A").Value
.Cells(X, "B").Value = Mid(FName, InStrRev(FName, ".") + 1)
Next
End With
End Sub

 

[Gord Dibben]

Do you want VBA?

You could do the same with Data>Text to Columns>Delimited by a period.


Gord Dibben MS Excel MVP

 

[StephCA]

Yes. Needed VBA to add to an already written macro. And Rick's code is very
quick! Didn't know Data>Text, though. Thanks much for the tip. Stephanie