Forum Discussion
workbook and sheet name via formula
Afraid for Excel Online that's only with Office Script, there are properties getName both for workbook and worksheet.
VBA and related functions don't work in Excel Online.
I'm trying for a solution that's backwards-compatible, eg. if you email the file to someone with Excel 365 (sadly, a common occurrence.)
I think I've found the glitch. It's not the FALSE command per se, it's that Excel inserts single-quotes when the sheet name begins with a number - which is my default, so larger workbooks are easier for users to navigate, especially if giving remote instruction over the phone or via email.
If the active sheet name does not have spaces and does not begin with a number, then Excel does not require the single quotes and the OP's formula will return a sheet name without them.
For sheet names beginning with numbers, or those with spaces, the OPs formula will return the quotes around the sheet name.
Hi MikeFromCanada 🙂 (I like the name).
If you want backward compatibility avoid Lambda expressions! Lambda is a new feature of the past 12 months or so. If you have someone with Excel 2019 or 2016 standalone version, Lambda does not exist. Excel 365 - do you mean the online version? For the online version / SharePoint you need a very different function, as CELL() does not work in those !
For the desktop Office 365 version, I have tried the:
=MID(CELL("filename",D1),FIND("]",CELL("filename",D1))+1,32)
on a sheet I named "3 Sheet", and that works. This formula will also work if you have 2016 or 2019 or 2022 stand-alone versions.
If you want to be versatile across Web / SharePoint and Desktop 365 or fixed versions then you need to use this:
=SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D1), "!"), "'", "" ), "=", "")
where the cell content of D1 ='3 Sheet'!D2 , and the format of D1 is set to 0;0;
That solution is universal. It is a bit messy as it uses 3 cells. You can use the Formulas> Evaluate Formula function to check how it works. This solution was posted somewhere higher up in this thread too.
Happy days to you from New Zealand 🙂
- 🙂 Fantastic, thanks - however, MikeFromCanada wants a function that works for all versions. TEXTSPLIT is new since 31 Mar 2022.If one just works in O365 then this is beautiful.