Replace Last occurrence of a character

Question

I have a spreadsheet with lots of paths to files in them. I use the following formula to replace the last occurrence of a "\" in each cell of a column. How can I change this to a macro so I don't have to paste in and drag down each column.

=SUBSTITUTE(K2,"\","?",LEN(K2)-LEN(SUBSTITUTE(K2,"\","")))

I tried to record a macro and this works but only on one cell and only if the active cell is in the O Column

Sub Macro4()
ActiveCell.FormulaR1C1 = _
    "=SUBSTITUTE(RC[-4],""\"",""?"",LEN(RC[-4])-LEN(SUBSTITUTE(RC[-4],""\"","""")))"
Range("O2").Select
End Sub

I need to have this put the value in O column starting at O2 for each non empty K starting with K2 regardless of what the active cell is.

Find the last row in K put it in a variable like `lstRw` then you can simply: `Range("O2:O" & lstrw).FormulaR1C1 = _ "=SUBSTITUTE(RC[-4],""\"",""?"",LEN(RC[-4])-LEN(SUBSTITUTE(RC[-4],""\"","""")))"` for how to find the last row see here: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba — Scott Craner, Sep 08 '16 at 14:40

score 1 · Accepted Answer · answered Sep 08 '16 at 16:16

1

Here is an alternative approach:

Sub LastSlash()
    Dim N As Long, i As Long, rng As Range, r As Range
    Dim rc As Long, L As Long, j As Long

    rc = Rows.Count
    Set rng = Intersect(ActiveSheet.UsedRange, Range("K2:K" & rc))
    N = Cells(rc, "K").End(xlUp).Row

    For Each r In rng
        s = r.Value
        L = Len(s)
        If InStr(1, s, "\") > 0 Then
            For j = L To 1 Step -1
                If Mid(s, j, 1) = "\" Then
                    Mid(s, j, 1) = "?"
                    Exit For
                End If
            Next j
        End If
        r.Offset(0, 4).Value = s
    Next r
End Sub

answered Sep 08 '16 at 16:16

Gary's Student

95,722
10
59
99

You could shorten that a bit - change your `L` variable to `L = InStrRev(s, "\")`, replace your `If...End If` block with `If L > 0 Then Mid(s, L, 1) = "?"` – Darren Bartrup-Cook Sep 08 '16 at 16:30
@DarrenBartrup-Cook Thanks...................but I fear that **L** would find the **first** slash rather than the **last** slash............ – Gary's Student Sep 08 '16 at 16:33
@Nolemonkey Thank you for the feedback. – Gary's Student Sep 08 '16 at 18:05
1

It wouldn't find the first slash with `InStrRev` - that returns the position of an occurrence of one string within another, _from the end of string_. – Darren Bartrup-Cook Sep 09 '16 at 08:01
@DarrenBartrup-Cook **You are correct!!** ..............I learned something new today...................Thanks – Gary's Student Sep 09 '16 at 11:08

Replace Last occurrence of a character

1 Answers1