Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stolen Patterns | Excel Discussion (Misc queries) | |||
SUBSTITUTE (more than one in Excel ?) | New Users to Excel | |||
can I customize cell fill patterns in Excel? | Excel Discussion (Misc queries) | |||
Excel cell patterns | Excel Discussion (Misc queries) | |||
Copying spreadsheet with fonts and patterns | Excel Discussion (Misc queries) |