Remove all text before character, when character appears multiple times

[ladyredbull115]

I have many rows that contain a file path which are all different and I need to end up with only the text after the last \

For example:

c:\users\user.name\appdata\roaming\baidu\baidurjdownloader\1.6.0.77\bdrcdl.exe The cells after the file path contain counts of times this location was accessed. Essentially, I am looking to remove all of the text before bdrcdl.exe so that I can create a pivot table and sum up all of the counts of bdrcdl.exe I tried =RIGHT(A29,LEN(A29)-FIND("",A29)) but that only removes the text before the first "" Converting the text to columns works, but since each file path is a different length, it spreads the text so that I can't easily gather what I need. Thinking I need to a macro to loop through the nested function I tried, but I am still new to VBA and not really sure where to start on that one. Any suggestions will be greatly appreciated.

<tbody>
</tbody>

 

[Rick Rothstein]

I have many rows that contain a file path which are all different and I need to end up with only the text after the last \

For example:

c:\users\user.name\appdata\roaming\baidu\baidurjdownloader\1.6.0.77\bdrcdl.exe The cells after the file path contain counts of times this location was accessed. Essentially, I am looking to remove all of the text before bdrcdl.exe so that I can create a pivot table and sum up all of the counts of bdrcdl.exe

<tbody>

You want to remove that text directly within the cells containing the text, correct? If so, select the column with your text, press CTRL+H to bring up Excel's Replace dialog box, put "*" (without the quote marks) in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure there is no check mark in the Checkbox labeled "Match entire cell contents", and finish off by clicking the "Replace All" button. If you really need this as a macro (and assuming your text is in Column A)... Sub GetFileName() Columns("A").Replace "*\", "", xlPart End Sub

 

[ladyredbull115]

MickG thanks very much. This worked as expected.

I Imagine you have some Blank rows !!!
Try this:-

[COLOR=Navy]Sub[/COLOR] MG29Nov18
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, Sp [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
 [COLOR=Navy]If[/COLOR] Not Dn.Value = vbNullString [COLOR=Navy]Then[/COLOR]
    Sp = Split(Dn.Value, "\")
    Dn.Value = Sp(UBound(Sp))
 [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[ladyredbull115]

Thanks AIKey, this also worked

=trim(right(substitute(a1,"\",rept(" ",len(a1))),len(a1)))

 

[MickG]

You're welcome

 

[ladyredbull115]

Thanks Rick Rosthstein, this also would work. I did not realize excel allowed the wildcard characters in the find and replace box.