SUBSTITUTE patterns?
 

I want to extract the worksheet name from cell("filename"). McGimpsey
has pointed me to
http://www.mcgimpsey.com/excel/formu..._function.html . (Thank
you very much.) That contains the following formula:

=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255)

That works just fine, of course. But I thought it would be "nicer" if
something like the following could be made to work:

=substitute(cell("filename",A1), "*]", "")

where "*]" is intended to mean "any number of characters up to and
including ]". Of course, that does not work as I intended.

Now, if there were an easier way, I'm sure McGimpsey would have thought
of it. But since SEARCH() has pattern-matching ability, I wondered if
perhaps I was overlooking something.

SUBSTITUTE patterns?
 

SUBSTITUTE() doesn't use wildcards. If F20 contains:
abc*der
then
=SUBSTITUTE(F20,"*","")
returns:
abcder

--
Gary's Student


" wrote:

I want to extract the worksheet name from cell("filename"). McGimpsey
has pointed me to
http://www.mcgimpsey.com/excel/formu..._function.html . (Thank
you very much.) That contains the following formula:

=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255)

That works just fine, of course. But I thought it would be "nicer" if
something like the following could be made to work:

=substitute(cell("filename",A1), "*]", "")

where "*]" is intended to mean "any number of characters up to and
including ]". Of course, that does not work as I intended.

Now, if there were an easier way, I'm sure McGimpsey would have thought
of it. But since SEARCH() has pattern-matching ability, I wondered if
perhaps I was overlooking something.

SUBSTITUTE patterns?
 

Gary''s Student wrote:
SUBSTITUTE() doesn't use wildcards. If F20 contains:
abc*der then =SUBSTITUTE(F20,"*","") returns: abcder

Of course I knew that. I wrote: "Of course, that does not work as I
intended." I hope my inquiry does not end with GS's comments. My
question was __not__ "does SUBSTITUTE allow wildcards". My question
was: is there __any_way__ to substitute patterns? (Other than the
MID/FIND combination that McGimpsey uses.)

SUBSTITUTE patterns?
 

checkout:

http://groups.google.com/group/micro...1f6ca30cbeee06


--
Gary's Student
gsnu200701


" wrote:

Gary''s Student wrote:
SUBSTITUTE() doesn't use wildcards. If F20 contains:
abc*der then =SUBSTITUTE(F20,"*","") returns: abcder

Of course I knew that. I wrote: "Of course, that does not work as I
intended." I hope my inquiry does not end with GS's comments. My
question was __not__ "does SUBSTITUTE allow wildcards". My question
was: is there __any_way__ to substitute patterns? (Other than the
MID/FIND combination that McGimpsey uses.)