Deleting unneeded characters in a cell

[Wally Steadman]

OK All,
I am just playing around learning this VBA stuff and decided to build a sub
routine that scans my hard drive for specific file types and puts them on
sheet1 of my spreadsheet and this works fine. I even have the second column
do a LEN so I know how many characters are in the path name, which leads to
the point I am at now and while I am continuing to work on it I think I need
some help. When the Sub Routine Runs, it lists my files as below:

C:\Documents and Settings\wfsteadman\Desktop\thumb1\text\10cloth.txt
C:\Documents and Settings\wfsteadman\Desktop\thumb1\text\10leather.txt
C:\Documents and Settings\wfsteadman\Desktop\thumb1\text\15cloth.txt
C:\Documents and Settings\wfsteadman\Desktop\thumb1\text\15leather.txt
C:\Program Files\Adobe\Photoshop 7.0\Presets\Layouts\1stFiveBySevens.txt
C:\Documents and Settings\wfsteadman\Desktop\thumb1\text\20cloth.txt

I am now wanting it to do a "right trim" I guess so that it populates column
3 with only the filename such as

10cloth.txt
10leather.txt
15cloth.txt
15leather.txt
1stFiveBySevens.txt
20cloth.txt

Then I can do a sort as on colum C and find duplicate files (Yes I know I
can just use FILE FIND, but I am doing this as a learning tool). I tried
doing the following to have it evaluate a characeter from right to left and
if it finds the "\" then it will delete everything to the left and including
the \ so that I am only left with the Filename. I am sure there are already
VBA functions out there that will do this but again, trying to learn and not
sure how to get it to evaluate just a character

C:\CBTLIB\CS1302E\CBTLIST.DBF 29


So in the above example the \ is at character 17 so I was thinking that when
it found the \ it would do a left delete to character 17 or something like
that.

Hope this was not too confusing.

TIA Wally Steadman

 

[Bob Phillips]

Dim pos As Long
Dim cell As Range

For Each cell In Selection
pos = InStrRev(cell.Value, "\")
If pos > 0 Then
cell.Value = Right(cell.Value, Len(cell.Value) - pos)
End If
Next cell


--

HTH

RP
(remove nothere from the email address if mailing direct)