Removing text before and after word in cell
 

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

Removing text before and after word in cell
 

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

Removing text before and after word in cell
 

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

Removing text before and after word in cell
 

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

Removing text before and after word in cell
 

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

Removing text before and after word in cell
 

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

Removing text before and after word in cell
 

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

Removing text before and after word in cell
 

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 -

Removing text before and after word in cell
 

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

Removing text before and after word in cell
 

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.



On Sep 15, 12:00*pm, Mike H wrote:
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(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 -

Removing text before and after word in cell
 

Hi,

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

Mike

" wrote:

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.



On Sep 15, 12:00 pm, Mike H wrote:
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(SUBSTITUTÂ*E(A1,"\",""))))),LEN(RIGHT(A1,LEN(A 1)-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 -

Removing text before and after word in cell
 

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

Removing text before and after word in cell
 

Hi Muppet Man,

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

Baffled!
--
Deirdre


" wrote:

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(A 1)-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 -