how get the word after last slash in a multi lined cell using excel vba

Question

I have a lot data in the mulitlined cell.I want the word after last slash in each line in a mutilined cell.I used this code Worksheets("Sheet1").Columns("A").Replace What:="*/", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True But it didn't worked.I want the format in the below image.Please help me out. word after last slash in a multilined cell

P.S.I am a beginner in excel vba

Xabier Xabier · Accepted Answer · 2018-05-16T11:23:16

I believe the following code will do what you expect it to:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set you worksheet
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get the last row with data on column A

For i = 1 To LastRow 'loop from row 1 to last
    ValueList = Split(ws.Cells(i, 1).Value, vbLf) 'split next line values into array
    For x = LBound(ValueList) To UBound(ValueList) 'loop through array
        pos = InStrRev(ValueList(x), "/") 'get the position of the last /
        ValueList(x) = Right(ValueList(x), Len(ValueList(x)) - pos) 'remove everything before the last /
        ws.Cells(i, 2).Value = ws.Cells(i, 2).Value & vbLf & ValueList(x) 'pass the newly created values into Column B
    Next x
Next i
End Sub

how get the word after last slash in a multi lined cell using excel vba

2 Answers