Create RegEx-pattern - TAB-name

Albireo · December 12, 2023, 9:04pm

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
'file:///X:/dr/own.ods'#$Sheet1

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

Albireo · December 20, 2023, 11:31pm

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 )

karolus · December 12, 2023, 9:42pm

Hallo

=REGEX(A1;"^.*#\$['’]?(\w+)['’]?(\.\w+)?$";"$1")

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

=REGEX(A1;"^.*?#\$['’]?([\w\s]+)['’]?(\.\w+)?$";"$1")

Albireo · December 12, 2023, 10:45pm

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

=REGEX(A1;"^.*?#$[’’]?([\w\s]+)[’’]?(.\w+)?$";"$1")

karolus · December 13, 2023, 6:31am

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

erAck · December 13, 2023, 8:06pm

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.

karolus · December 13, 2023, 8:36pm

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!

Maybe

=REGEX(A1;"^.*?#\$['’]?([\w\s\W]+)['’]?(\.\w+)?$";"$1")

erAck · December 14, 2023, 3:04pm

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.