Create RegEx-pattern - TAB-name

My desire is to select the name of the tab using RegEx
The example of file names below also contain the tab names .:

'file:///C:/Temp/TestCalc 2023.ods'#$52
'file:///C:/Temp/TestCalc 2023.ods'#$’52’
'file:///C:/Temp/TestCalc 2023.ods'#$’52’.Q21

How should a RegEx pattern be formed in Calc,
so that the result is the name of the tab? (52 and Sheet1)

Thanks for the replies and ideas and inspiration about RegEx.
Trying to resolve another request (in the same ods files)
Which causes new conditions.

Suppose the the formula Cell("filename)) give the result / filename.: 'file:///C:/Temp/TestCalc 2023.ods'#$52

I want to retrieve the value from a cell (e.g. C5 in tab Start) in another ods file with the name .: C:/Temp/TestCalc 2024.ods (2024 instead of 2023 - otherwise same path)
Since each time the formula is to be used, the tab and cell must be manually entered into the formula, I tried to make it easy to find where in the formula this data should be entered.

This became my proposed solution. (with multiple RegEx / Concatenate and Substitute)

=INDIRECT(CONCATENATE(SUBSTITUTE(REGEX(CELL("filename");  "^(.+/[^/]+.ods').*";   "$1");   CONCATENATE(REGEX(CELL("filename");".*(\d{4}).*'#\$.*";"$1");   ".ods");   CONCATENATE(REGEX(CELL("filename");".*(\d{4}).*'#\$.*";"$1")+1;   ".ods"));  "#$'";   "Start"   ;"'.";   "C8"   ))

Summary .:

  • My wish is to replace the last year, e.g. 2023 vs. 2024 or 2022 (year +1 or -1)
  • From result from formula Cell(“filename”) Delete existing tabname (#$52)
  • As well as being able to easily supplement the formula with a desired Tab- and Cell- name. (in the example Tab .: Start / Cell .: C5 )



Edit: To match also whitespace in Sheetnames, like Sheet 42:


Thank you - Perfect!
Exactly what I was looking for.
Can you explain the last "$1" in (replace)


…means: return the 1st group between ( ), here it was [\w\s]+ →any combination of alphanumeric- and space-characters

Caveat, the [\w\s] set does not match sheet name allowed characters like
and many more. If the sheet name contains any of those (e.g. la'la) then the result is the entire reference string.

Not exactly true… the _underscore is in the \w -set!

I’m aware of these characters which are allowed in Sheetnames, but (except _ ) IMHO they should NOT be used in sheetnames!



Fwiw, I’ve seen at least ' apostrophe and related right quotation mark and () parentheses and & ampersand and - hyphen minus and . dot and , comma regularly in the wild quite often.