asked on

Extracting substrings using a formula

Dear Experts:

I got the following file paths entered in column A

M:\KLS_General\DMD\PM_1\MyFile_Final_2.pdf
M:\KLS_General_17\DMD\PM_2_3\MyFile2.pdf
M:\KLS_General_2\DMD\PM_4_5_6\MyFile6_Final_23072017.pdf

Is it possible to extract the substrings after the fourth (4th) back slash using a formula. I got a macro for this purpose but I wonder whether the same could be achieved using a formula, ie. applying the formula the results would be as follows:

MyFile_Final_2.pdf
MyFile2.pdf
MyFile6_Final_23072017.pdf

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas

Shums Faruk

Assuming You have data in A1 , try below in B1 and drag down:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1),"\",REPT(" ",99)),99))

Open in new window

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

Just realised that Shums' suggestion would also do last \ rather than specific and his formula is much shorter.

Andreas Hermle

ASKER

Incredible, the speed with which you come up with your solutions. By the way, all of them work just fine.

Since I can learn from all of them I suggest givinig all of you points. I hope Shums does not mind, although his formula is the most concise (as Rob commented) and he was the quickest to answer.

Thank you very much for your great and professional help. I really appreciate it. Regards, Andreas

Andreas Hermle

ASKER

Great jobs from all of you. Thank you very much.

Regards, Andreas

Roy Cox

You close the question and award the points.Divide the points by 4, one for each answer should be fair

Rob Henson

No worries, glad to assist.

Andreas Hermle

ASKER

Hi Roy,

uppps, just saw your comment, as a matter of fact I did not awardthe points in equal shares. Hope you don't mind. It is always hard to award points equitably. Maybe I should have awarded the points as you suggested.

Regards, Andreas

Shums Faruk

Hi Andreas,

You're welcome! Glad I was able to assist you.

I think you took the right decision, because in other solution you need to count the number of "\"

Thanks Rob for justifying.