Excel macro

[mtghr]

hi i have excel file with a few 1000 records this is how they look
/1/0/105.jpg
/7/4/7460.jpg
/7/4/7470.jpg
/3/2/3240-1.jpg
/X/2/X25.jpg
/3/2/320.jpg
/s/3/s35.jpg
/f/i/file_3.jpg
/4/1/4150.jpg
/1/0/104.jpg

now i want to keep only the last part of the record
/104.jpg
/4150.jpg

any help???? or hint?

 

[mintjulep]

Data | Text to columns.

 

[osx99]

or possibly use a UDF

in a MODULE enter

Function textSplit(ByVal text As String)
Dim i() As String

i = Split(text, "/")
textSplit = "/" & i(UBound(i))

End Function

Then use =textsplit(cell) in Excel.

Eg if "/1/0/105.jpg" is in cell A1 then

=textsplit(A1) returns /105.jpg

 

[xlbo]

As a one off job:

For Each c In Selection
c.Value = Right(c.Value, Len(c.Value) - InStrRev(c.Value, "/") + 1)
Next

As a UDF:

Function LastPart(strText As String, Optional KeepSlash As Boolean)

Dim incr As Integer

If KeepSlash Then incr = 1 Else incr = 0

LastPart = Right(strText, Len(strText) - InStrRev(strText, "/") + incr)

End Function

A1 = "/3/2/3240-1.jpg"
=LastPart(A1) = "3240-1.jpg"
=LastPart(A1,FALSE) = "3240-1.jpg"
=LastPart(A1,TRUE) = "/3240-1.jpg"


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question