Remove all text before character, when character appears multiple times

ladyredbull115

New Member
Joined
Nov 29, 2016
Messages
9
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:

[TABLE="width: 1790"]
<tbody>[TR]
[TD="width: 1790"]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.

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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 \
 
Last edited:
Upvote 0
Try this for FilePaths in column "A"
Code:
[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
 
Upvote 0
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 ""
 
Last edited:
Upvote 0
Thanks gaz_chops. That formula returns #value

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

Code:
[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]
 
Last edited:
Upvote 0
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"
Code:
[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
 
Upvote 0
Getting closer, updated formula,

returns this in two separate cells where the original file path was the same except for user name, a
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\bdrcdl.exe[/TD]
[/TR]
[TR]
[TD]\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\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 \[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I Imagine you have some Blank rows !!!
Try this:-
Code:
[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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top