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" wrote:

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

--
Deirdre


"Mike H" wrote:

Hi,

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

Mike

"Diddy" wrote:

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


--
Deirdre