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

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

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

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

Thanks Mike,

I'll give it a try and let you know :-)
--
Deirdre


"Mike H" wrote:

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

Maybe a little shorter...

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

Take care.
Muppet Man

On Sep 15, 11:35*am, Diddy wrote:
Thanks Mike,

I'll give it a try and let you know :-)
--
Deirdre



"Mike H" wrote:
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(SUBSTITUT*E(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- Hide quoted text -

- Show quoted text -

Works beautifully!

Thank you
--
Deirdre


"Mike H" wrote:

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

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

Mike

"Diddy" wrote:

Works beautifully!

Thank you
--
Deirdre


"Mike H" wrote:

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

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
"Diddy" wrote in message
...
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

Thank you Mike,

That's exactly what I needed :-)

Thanks again

--
Deirdre


"Mike Fogleman" wrote:

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
"Diddy" wrote in message
...
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