vba to get file extension from col A file names
 

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

vba to get file extension from col A file names
 

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

--
Rick (MVP - Excel)


"StephCA" wrote in message
...
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

vba to get file extension from col A file names
 

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" wrote:

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

--
Rick (MVP - Excel)


"StephCA" wrote in message
...
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

vba to get file extension from col A file names
 

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 (MVP - Excel)


"StephCA" wrote in message
...
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" wrote:

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

--
Rick (MVP - Excel)


"StephCA" wrote in message
...
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

vba to get file extension from col A file names
 

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"StephCA" wrote in message
...
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

vba to get file extension from col A file names
 

Do you want VBA?

You could do the same with DataText to ColumnsDelimited by a period.


Gord Dibben MS Excel MVP

On Fri, 3 Oct 2008 16:26:00 -0700, StephCA
wrote:

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

vba to get file extension from col A file names
 

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

"Gord Dibben" wrote:

Do you want VBA?

You could do the same with DataText to ColumnsDelimited by a period.


Gord Dibben MS Excel MVP

On Fri, 3 Oct 2008 16:26:00 -0700, StephCA
wrote:

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