Try:

Sub GetFileUntestedCode()
For i=1 to 3
with Sheets(i)
for each x in .range("B1:B" & .range(B65536").end(xlup).row) 'note the
leading "." before Range
x.value=dir(x.value)
Next
End With
Next
End Sub

If you have a valid path in a string, then Dir(thatString) will return the
last piece.

Bob Umlas
Excel MVP


"Lillian" wrote in message
...
I have one excel spreed sheet, it have three worksheet,
they are sheet1, sheet2, sheet3, in each worksheets on
the column B each row has real long file directory,
example:
J:\files\docfiles\amaya01\demand.mcp.wpd, all I need is
last file name: demand.mcp.wpd, it means I only need
the file name after the last slash "\", all the column B
has different rows of file name another example:
j:\FILES\DOCFILES\Civil Service\CS-SUBIA\OLGA.SRP, all
I need is OLGA.SRP file name, so can we write the macro
removed everything before last slash "\".

SO this macro has be in the loop for
sheet1,sheet2,sheet3.

thanks for the help.

Lillian