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>

 

[gaz_chops]

Welcome to the forum.

If a formula will suffice, try

=MID(A1,FIND("§",SUBSTITUTE(A1,"","§",LEN(A1)-LEN(SUBSTITUTE(A1," \ ",""))))+2,99)

Remove the space either side of \

 

[MickG]

Try this for FilePaths in column "A"

[COLOR="Navy"]Sub[/COLOR] MG29Nov51
[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
    Sp = Split(Dn.Value, "\")
    Dn.Value = Sp(UBound(Sp))
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ladyredbull115]

Thanks gaz_chops. That formula returns #value

 

[AlKey]

ADVERTISEMENT

If you need text after the last "" use this
Assuming string is in A1
=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT(" ",LEN(A1))),LEN(A1)))

*** something wrong with this forum software. Enter \ in ""

 

[gaz_chops]

Thanks gaz_chops. That formula returns #value

Did you remove the space either side of \ in the formula?

[TABLE="width: 552"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]c:\users\user.name\appdata\roaming\baidu\baidurjdownloader\1.6.0.77\bdrcdl.exe[/TD]
[TD][/TD]
[TD]drcdl.exe[/TD]
[/TR]
[TR]
[TD]c:\users\user.name\appdata\roaming\baidu\1.6.0.77\bdrcdl.exe[/TD]
[TD][/TD]
[TD]drcdl.exe[/TD]
[/TR]
</tbody>[/TABLE]

 

[ladyredbull115]

ADVERTISEMENT

Thanks MickG,

I added that code to the macro list.

When I ran it, I got a "Run-time error '9': subscript out of range" error, debugging shows Dn.Value = SP(UboundOSp)) highlighted.

Try this for FilePaths in column "A"

[COLOR=Navy]Sub[/COLOR] MG29Nov51
[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
    Sp = Split(Dn.Value, "\")
    Dn.Value = Sp(UBound(Sp))
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[ladyredbull115]

Getting closer, updated formula,

returns this in two separate cells where the original file path was the same except for user name, a

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\bdrcdl.exe

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\bdrcdl.exe Strange that it comes back with a different number or \s when the path was the same except for the user name field. I could use that and do a file find and replace on \

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[MickG]

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

 

[AlKey]

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