Removing text before and after word in cell

D

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 :)
 
M

Mike H

Hi,

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

Mike
 
D

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
 
M

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
 
D

Diddy

Works beautifully!

Thank you
--
Deirdre


Mike H said:
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
Click to expand...
 
A

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
 
M

Mike Fogleman

Here is some code you may be able to use within your routine:

Dim rng As Range, c As Range
Dim pos1 As Integer, pos2 As Integer
Set rng = Range("A1:A10") 'your range
For Each c In rng
pos1 = InStrRev(c.Value, "\", , vbTextCompare)
pos2 = InStrRev(c.Value, ".", , vbTextCompare)
c.Value = Mid(c.Value, pos1 + 1, pos2 - pos1 - 1)
Next

Mike F
 
A

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.
 
M

Mike H

Hi,

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

Mike
 
D

Diddy

Hi Muppet Man,

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

Baffled!
 
Top