SUBSTITUTE patterns?

N

nomail1983

I want to extract the worksheet name from cell("filename"). McGimpsey
has pointed me to
http://www.mcgimpsey.com/excel/formulae/cell_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.
 
N

nomail1983

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

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.)
 
Top