Removing text before and after word in cell

[Diddy]

Hi there,

I'm very gratefully using Ron De Bruin's merge worksheets code to collate
data from many workbooks.

The code gives you the option of putting path and filename in column A. I
don't want to alter this code but I would like to be able to go back to col A
and remove the path leaving just filename without the .xls extension.

I have skimmed through some examples of cutting out text but head's buzzing
(doing well to have figured out which bits of code to use in Ron's examples -
still very much a beginner!) and totally confused this am.

If anyone can help me that would be brilliant

 

[Mike H]

Hi,

For those of us not familiar with that code can we see what you have in
column A?

Mike

 

[Diddy]

Hi Mike,

Here it is

C:\TESTING\Data4-5\Winbourne.xls

In this eg the workbook is called Winbourne and this is the text I would
like to keep.

Many thanks

 

[Mike H]

Hi,

There simply has to be a better way but I can't see it so try this until
someone posts something a bit shorter. Put this in A1 and drag down. You can
then hide the column with the full path in.

=LEFT(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))),LEN(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))))-4)

Mike

 

[Diddy]

Thanks Mike,

I'll give it a try and let you know

 

[Diddy]

Works beautifully!

Thank you
--
Deirdre

Hi,

There simply has to be a better way but I can't see it so try this until
someone posts something a bit shorter. Put this in A1 and drag down. You can
then hide the column with the full path in.

=LEFT(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))),LEN(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))))-4)

Mike

 

[Mike H]

Glad I could help but I'm still hoping to see something shorter.

Mike

 

[a.muppet.man]

Maybe a little shorter...

=SUBSTITUTE(RIGHT(A1,LEN(A1)-LOOKUP(9.9999999999E
+307,FIND("\",A1,ROW($1:$1024)))),".xls","")

Take care.
Muppet Man

 

[a.muppet.man]

Mike, see the solution above... which could equally be shortened
further to

=SUBSTITUTE(RIGHT(A1,LEN(A1)-
LOOKUP(99^99,FIND("\",A1,ROW($1:$1024)))),".xls","")

and if you werent worried about .xls at the end...

=RIGHT(A1,LEN(A1)-LOOKUP(99^99,FIND("\",A1,ROW($1:$1024))))

Muppet Man.

 

[Mike H]

Hi,

The OP didn't want the .xls at the end but I like your formula that removes
that.

Mike

 

[Diddy]

Thank you Mike,

That's exactly what I needed

Thanks again

 

[Diddy]

Hi Muppet Man,

This worked a treat, but how does it do it? What's it looking up where?

Baffled!