The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,854 other subscribers
«

Excel: operations involving the last occurence of a substring (with examples getting the current Worksheet, Workbook and file/directory path)

Posted by jpluimers on 2026/04/02

Last month I wrote about CELL function looks interesting but beware: language nightmares coming up….

There I mentioned both formulas from [Wayback/Archive] Get sheet name only – Excel formula | Exceljet and [Wayback/Archive] Insert the current Excel file name, path, or worksheet in a cell – Microsoft Support failing to deliver the expected results

an official Microsoft example of obtaining the Worksheet name which I suspected would not work on systems allowing ] in path names. And indeed it is true: that case fails on both MacOS and Windows in the same way.

Here you see the failures of both the ExcelJet function and the ones from Microsoft Support on two operating systems:

Formula MacOS Outcome Windows Outcome
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) :[Workbook1.xlsx]Sheet1 \[Book1.xlsx]Sheet1
=MID(CELL("filename"),FIND(".xlsx]",CELL("filename"))+1,255) xlsx]:[Workbook1.xlsx]Sheet1 xlsx]\[Book1.xlsx]Sheet1
=RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255))))) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[Workbook1.xlsx]Sheet1
 Users\jeroenp\[Downloads.xlsx]\[Book1.xlsx]Sheet1
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1) Downloads.xlsx Downloads.xlsx
=CELL("filename") Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[Workbook1.xlsx]Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\[Book1.xlsx]Sheet1

What you can see here is that:

  • Excel on MacOS uses the semicolon ":" as path separator (though MacOS itself uses the slash "/" for that)
  • Excel on Windows uses the backslash "\" as path separator which Windows itself uses as well
  • Excel uses square brackets "[" and "]" to surround the Workbook name which you can use to separate the three sections directory path, Workbook name (including extension) and Worksheet name
  • Formulas fail when
  • The ExcelJet formula, and likely Microsoft formulas, fail if you use a longer delimiter like ".xlsx]" (that sounds fabricated, but I write this post in part so it can be applied in cases where longer delimiters are needed)

The ExcelJet formulas has another problem which does not surface here as it presumes a hard limitation of 255 characters on the Worksheet name. The

The second issue is sort of OK as the Excel UI imposes an artificial limit of 31-characters whereas the file format allows for 255:

The second issue however will become a problem when Excel removes that limit. Replacing 255 by LEN(CELL("filename")) which will always be large enough to contain the Worksheet name.

The reason I stress this formula so much is that it is used everywhere, just look at these search results:

Compare that to the queries from the Microsoft documentation having a much smaller proliferation:

Given the proliferation of =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255), it needs to be debunked, so I tweeted [Wayback/Archive] Jeroen Wiert Pluimers @wiert@mastodon.social on Twitter: “Whoever introduced <code>=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)</code> to get the current Excel Worksheet name should be spanked: it fails on MacOS & Windows when your Workbook path contains <code>]</code>. It proliferated like crazy though: ~500k results from …” in the [Wayback/Archive] Thread by @jpluimers on Thread Reader App that announced an upcoming blog post which you are reading right now.

Huh, [ and ] can’t be delimiters, right?

I produced the above problem by putting Workbooks on both MacOS and Windows into a directories called [Downloads.xlsx], which contains the same markers [ and ] that Excel uses to surround the Workbook name.

Contrary to what many expect, both markers [ and ] are in fact valid pathname characters on both MacOS and Windows as per [Wayback/Archive] Allowed characters in filename – Stack Overflow (thanks [Wayback/Archive] python dude and [Wayback/Archive] CpILL):

OK, so looking at Comparison of file systems if you only care about the main players file systems:

so any byte except NUL, \, /, :, *, ?, ", <, >, | and you can’t have files/folders call . or .. and no control characters (of course).

The background material in that answer comes from these Wikipedia entries:

So what if a Sheet name can include [ or ]?

On Windows when you try to rename a sheet and include [ or ], the rename just gets cancelled without notification.

On MacOS however, you get this nice error message:

While renaming a sheet or chart, you entered an invalid name.

Try one of the following:

  • Make sure the name you entered does not exceed 31 characters.
  • Make sure the name does not contain any of the following characters: : \ / ? * [ or ]
  • Make sure you did not leave the name blank.

OK

It means that you are safe: the last [ and ] are always the ones surrounding the Workbook name in CELL("filename").

Towards a better function

Excel versions 365 and up had TEXTAFTER and TEXTBEFORE functions introduced in 2022 that makes the below a lot easier, but as many shops still run Excel 2021 or older, here are solutions with the older functions.

I will start with the .xlsx] last delimiter for he Worksheet name, then work back to just the ] as that delimiter. My article would have been a lot harder without the StackOverflow answer [Wayback/Archive] Excel: last character/string match in a string – Stack Overflow (thanks [Wayback/Archive] geotheory and especially [Wayback/Archive] tigeravatar) which I have put in its own section further below.

It explains the use of =FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\"))) and I bumped into it via [Wayback/Archive] excel – Split string at last (or fourth) occurence of “.” delimiter – Stack Overflow (thanks [Wayback/Archive] user1004413 and [Wayback/Archive] dosdel):

Q

I like to delimit the string as follow

Given the following String :

Column 1    
10.80.111.199.1345
127.0.0.1.3279

I will like to delimit numbers after the last “.”, which will get the follow output

 Column 1       Column 2               

10.1.12.5       1345
127.0.0.1       3279

…

A

… if it needs to be more dynamic then these formulas will work (Reference: Excel: last character/string match in a string)

=LEFT(A1,FIND("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1)

returns “127.0.0.1”

…

The foundation of the formula to obtain parts of delimited strings is to:

  1. count the number of substrings
  2. replace the last substring with a character never used in the string
  3. search inside the replaced string and grab the required portions using the LEFT and/or MID functions

Based on the above and that on MacOS Excel uses : as path delimiter instead of /, the replacement character in 2. can be

  • / for Windows
  • / for MacOS

That’s convenient, as both are the same!

Count the number of substrings

Let’s start with the this formula from the below StackOverflow answer and dissect it bit by bit from back to front: (len(string)-len(substitute(string, substring, "")))\len(substring)

I’m doing this on MacOS but Windows gives similar results (I might merge the two into one table eventually)

Formula MacOS Outcome Windows Outcome
=LEN(".xlsx]") 6 6
=CELL("filename") Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[Workbook1.xlsx]Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\[Book1.xlsx]Sheet1
=SUBSTITUTE(CELL("filename"),".xlsx]","") Macintosh HD:Users:jeroenp:[Downloads:[Workbook1Sheet1 C:\Users\jeroenp\[Downloads\[Book1Sheet1
=LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")) 54 40
=(LEN(CELL("filename"))) 66 52
=(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]",""))) 12 12
=(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]") 2 2

The outcome is that there are 2 occurrences of .xlsx], in the full CELL("filename") which is correct.

Now with substring ] instead of .xlsx], the length goes down from 6 to 1 so the /LEN(".xlsx]") is not needed:

Formula Outcome MacOS Outcome Windows
=LEN("]") 1 1
=CELL("filename") Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[Workbook1.xlsx]Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\[Book1.xlsx]Sheet1
=SUBSTITUTE(CELL("filename"),"]","") Macintosh HD:Users:jeroenp:[Downloads.xlsx:[Workbook1.xlsxSheet1 C:\Users\jeroenp\[Downloads.xlsx\[Book1.xlsxSheet1
=LEN(SUBSTITUTE(CELL("filename"),"]","")) 64 50
=(LEN(CELL("filename"))) 66 52
=(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]",""))) 2 2
=(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]") 2 2

The outcome is that there are 2 occurrences of ], in the full CELL("filename") which, like above, is correct.

In addition, the outcome is reached one formula earlier.

Replace the last substring with /

First we replace the last substring with /. This gives us an anchorpoint to continue processing:

For longer substring .xlsx] it looks like this:

Formula Outcome MacOS Outcome Windows
=SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]")) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[Workbook1/Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\[Book1/Sheet1x

For the final substring ] like this either with or without the length division:

Formula Outcome MacOS Outcome Windows
=SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[Workbook1.xlsx/Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\[Book1.xlsx/Sheet1
=SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[Workbook1.xlsx/Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\[Book1.xlsx/Sheet1

This gives us .../Sheet1.

Take outcome relative to the replaced /

Then we find the character after the last substring as otherwise we would start too early and the Worksheet name is too long:

Formula Outcome MacOS Outcome Windows
=FIND("/",SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]"))) 55 41
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]"))),LEN(CELL("filename"))) .xlsx]Sheet1 .xlsx]Sheet1

The solution is to add the length of ".xlsx]" to the FIND result with +LEN(".xlsx]"):

Formula Outcome MacOS Outcome Windows
=FIND("/",SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]")))+LEN(".xlsx]") 61 47
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]")))+LEN(".xlsx]"),LEN(CELL("filename"))) Sheet1 Sheet1

 

The same problem holds for the substring "]":

Formula Outcome MacOS Outcome Windows
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]"))) 60 46
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]",""))))) 60 46
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]"))),LEN(CELL("filename"))) ]Sheet1 ]Sheet1
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]",""))))),LEN(CELL("filename"))) ]Sheet1 ]Sheet1

With a substring "]" you can either add 1 or LEN("]") to it. I find the latter more clear, but the results are the same:

Formula Outcome MacOS Outcome Windows
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))+LEN("]") 61 47
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))+LEN("]") 61 47
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))+1 61 47
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))+1 61 47
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))+LEN("]"),LEN(CELL("filename"))) Sheet1 Sheet1
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))+LEN("]"),LEN(CELL("filename"))) Sheet1 Sheet1
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))+1,LEN(CELL("filename"))) Sheet1 Sheet1
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))+1,LEN(CELL("filename"))) Sheet1 Sheet1

So now you know what the +1 in many Excel text after last delimiter examples comes from (:

The formula to the current Worksheet name now is this:

=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))+1,LEN(CELL("filename")))

or in case you have a delimiter that is multiple characters long:

=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))+LEN("]"),LEN(CELL("filename")))

Getting the directory

Getting the directory of a Worksheet works in a similar way, but searching for the last [ instead of ], and then grabbing everything until the last [ using the LEFT function.

Below is how we get there using the same steps as above. I will leave out longer substrings and will focus on [, but I will include examples using the length divisions in case you ever need them.

Count the number of substrings

I include the division with the length of the substring in case you ever need longer substrings.

Formula Outcome MacOS Outcome Windows
=LEN("[") 1 1
=CELL("filename") Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[Workbook1.xlsx]Sheet1 D:\Users\jeroenp\[Downloads.xlsx]\[Book1.xlsx]Sheet1
=SUBSTITUTE(CELL("filename"),"[","") Macintosh HD:Users:jeroenp:Downloads.xlsx]:Workbook1.xlsx]Sheet1 D:\Users\jeroenp\Downloads.xlsx]\Book1.xlsx]Sheet1
=LEN(SUBSTITUTE(CELL("filename"),"[","")) 64 50
=(LEN(CELL("filename"))) 66 52
=(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[",""))) 2 2
=(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[") 2 2

The outcome is that there are 2 occurrences of [, in the full CELL("filename") which, like above, is correct.

In addition, the outcome is reached one formula earlier.

Replace the last substring with /

Formula Outcome MacOS Outcome Windows
=SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:/Workbook1.xlsx]Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\/Book1.xlsx]Sheet1
=SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:/Workbook1.xlsx]Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\/Book1.xlsx]Sheet1

Now we get the .../Workbook1.xlsx]Sheet1 or .../Book1.xlsx]Sheet1 to continue processing.

Take outcome relative to the replaced /

Then we find the character before the last substring as otherwise we would start too late and the Directory path is too long:

Formula Outcome MacOS Outcome Windows
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("["))) 45 35
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[",""))))) 45 35
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[ C:\Users\jeroenp\[Downloads.xlsx]\[
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[ C:\Users\jeroenp\[Downloads.xlsx]\[

Indeed, like with ] we are one off here, but now in the opposite direction and not off by 1, but off by 2 so we need to subtract the length of the delimiter [ and path separator (which is ': on MacOS and \ on Windows, so we take the / substitution character for it or just the number 2):

Formula Outcome MacOS Outcome Windows
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-LEN("[")-LEN("/") 43 33
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))-LEN("[")-LEN("/") 43 33
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-1-1 43 33
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))-1-1 43 33
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-2 43 33
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))-2 43 33
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-LEN("[")-LEN("/")) Macintosh HD:Users:jeroenp:[Downloads.xlsx] C:\Users\jeroenp\[Downloads.xlsx]
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))-LEN("[")-LEN("/")) Macintosh HD:Users:jeroenp:[Downloads.xlsx] C:\Users\jeroenp\[Downloads.xlsx]
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-1-1) Macintosh HD:Users:jeroenp:[Downloads.xlsx] C:\Users\jeroenp\[Downloads.xlsx]
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))-1-1) Macintosh HD:Users:jeroenp:[Downloads.xlsx] C:\Users\jeroenp\[Downloads.xlsx]
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-2) Macintosh HD:Users:jeroenp:[Downloads.xlsx] C:\Users\jeroenp\[Downloads.xlsx]
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))-2) Macintosh HD:Users:jeroenp:[Downloads.xlsx] C:\Users\jeroenp\[Downloads.xlsx]

Look at the permutations! But now you at least know where the number 2 of the final formula comes from:

=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))-2)

or in case you have a delimiter that is multiple characters long:

=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-LEN("[")-LEN("/"))

Getting the Workbook filename (without directory path)

Part 1: getting the starting point

For this case we want the text which begins after the start delimiter [, so we first start removing the -LEN("/") subtraction from the above =FIND formula

=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-LEN("[")

into

=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))

To verify this is not enough, observe the where we start with the above formula and transition from using LEFT to MID. The first two MID results include the [, but when adding +LEN("[") to the FIND the start is correct: on MacOS Workbook1 and on Windows Book1.

Formula Outcome MacOS Outcome Windows
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("["))) 45 35
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[",""))))) 45 35
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[ C:\Users\jeroenp\[Downloads.xlsx]\[
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))) Macintosh HD:Users:jeroenp:[Downloads.xlsx]:[ C:\Users\jeroenp\[Downloads.xlsx]\[
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("["))),LEN(CELL("filename"))) [Workbook1.xlsx]Sheet1 [Book1.xlsx]Sheet1
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[",""))))),LEN(CELL("filename"))) [Workbook1.xlsx]Sheet1 [Book1.xlsx]Sheet1
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))+LEN("[") 46 36
=FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))+LEN("[") 46 36
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))+LEN("[")) Workbook1.xlsx]Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\[B
=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))+LEN("[")) Workbook1.xlsx]Sheet1 C:\Users\jeroenp\[Downloads.xlsx]\[B
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))+LEN("["),LEN(CELL("filename"))) Workbook1.xlsx]Sheet1 Book1.xlsx]Sheet1
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))+LEN("["),LEN(CELL("filename"))) Workbook1.xlsx]Sheet1 Book1.xlsx]Sheet1

So the starting point of the Workbook filename is determined with either of these formulas of which you can use the first when you have longer delimiters:

  1. =FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))+LEN("[")
  2. =FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))+LEN("[")

Part 2: finding the ending point

For the end of the Workbook filename is the last delimiter ] we can start with the functions that got us the Worksheet name, but start one character earlier:

Formula Outcome MacOS Outcome Windows
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]"))) 60 46
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]",""))))) 60 46
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]"))),LEN(CELL("filename"))) ]Sheet1 ]Sheet1
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]",""))))),LEN(CELL("filename"))) ]Sheet1 ]Sheet1

Here again two formulas that gets the correct position where the first can be used if the delimiter is longer than one character:

  1. =FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))
  2. =FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))

Part 3: finding the length

Since we use a MID function that gets the Workbook name from the starting point requires a length, let’s calculate that:

  1. take the position of the finishing delimiter ]
  2. =FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))
  3. subtract the position after the starting delimiter [
  4. =FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))+LEN("[")

Since when subtracting a + becomes -, the resulting formula for the length is this when the delimiters can be longer than one character:

=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))
-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-LEN("[")

or when it is always a single character:

=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))
-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))-LEN("[")

You can verify the results in this table:

Formula Outcome MacOS Outcome Windows
=LEN("Workbook1.xlsx") 14
=LEN("Book1.xlsx") 10
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))
-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))
-LEN("[")		 14 10
=FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))
-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))
-LEN("[")		 14 10

Part 4: combining the above for a MID expression

The final formula becomes even longer than the above FIND formula so in the table I have split them across several lines; the first three rows contain the extension and the second group omits the extension:

Formula Outcome MacOS Outcome Windows
=MID(CELL("filename")
,FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))+LEN("[")
,FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))
-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))
-LEN("["))		 Workbook1.xlsx Book1.xlsx
=MID(CELL("filename")
,FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))+LEN("[")
,FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))
-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))
-LEN("["))		 Workbook1.xlsx Book1.xlsx
=MID(CELL("filename")
,FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))+1
,FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))
-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))
-1)		 Workbook1.xlsx Book1.xlsx
=MID(CELL("filename")
,FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))+LEN("[")
,FIND("/",SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]")))-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))
-LEN("["))		 Workbook1 Book1
=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))+LEN("[")
,FIND("/",SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]")))
-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))
-LEN("["))		 Workbook1 Book1
=MID(CELL("filename")
,FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))+1
,FIND("/",SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]")))
-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))
-1)		 Workbook1 Book1

These complex formulas were exactly the reason why I wish functions for these had been built into Excel a long time ago, hence this tweet in the thread I mentioned above:

[Wayback/Archive] Jeroen Wiert Pluimers @wiert@mastodon.social on Twitter: “The long formulas for getting the Worksheet name and Workbook directory path are short compared to the formula for getting the Workbook filename: that formula is close to 400 characters and requires alt-text. Which is why Excel should have had formulas for those 10+ years ago.”

=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))+1,FIND("/",SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]")))-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))))-LEN("["))

The base for the above formulas

This led me to all of the above [Wayback/Archive] Excel: last character/string match in a string – Stack Overflow (thanks [Wayback/Archive] geotheory and especially [Wayback/Archive] tigeravatar) to which I added some extra formatting:

Q

Is there an efficient way to identify the last character/string match in a string using base functions? I.e. not the last character/string of the string, but the position of a character/string’s last occurrence in a string. Search and find both work left-to-right so I can’t think how to apply without lengthy recursive algorithm. And this solution now seems obsolete.

A

I think I get what you mean. Let’s say for example you want the right-most \ in the following string (which is stored in cell A1):

Drive:\Folder\SubFolder\Filename.ext

To get the position of the last \, you would use this formula:

=FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))

That tells us the right-most \ is at character 24. It does this by looking for "@" and substituting the very last "\" with an "@". It determines the last one by using

(len(string)-len(substitute(string, substring, "")))\len(substring)

In this scenario, the substring is simply "\" which has a length of 1, so you could leave off the division at the end and just use:

=FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))

Now we can use that to get the folder path:

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

Here’s the folder path without the trailing \

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)

And to get just the filename:

=MID(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

However, here is an alternate version of getting everything to the right of the last instance of a specific character. So using our same example, this would also return the file name:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

Some people find the =FIND explanation easier from [Wayback/Archive] microsoft excel – I need to find the last Space in a XLS Cell – Super User (thanks [Wayback/Archive] Dheer and [Wayback/Archive] m4573r)

A

you can use this formula (assuming the test you want to find the last space in is in A1):
=FIND("☃",SUBSTITUTE(A1," ","☃",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
You can divide it into 3 parts:
  • LEN(A1)-LEN(SUBSTITUTE(A1," ","")) gives you the number of spaces, let’s call it x,
  • SUBSTITUTE(A1," ","☃",[x]) will replace the xth space (so the last one) by a snowman,
  • FIND("☃",[...]) will give you the position of the snowman. Which is the position of the last space.

There are also FILTERXML ways, but those only work on Windows. I’ll put a few links in a future blog post.

The LET function

The above formulas can be simplified by using the [Wayback/Archive] LET function – Microsoft Support

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac

…

To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126.LET diagramBenefits

  • Improved Performance If you write the same expression multiple times in a formula, Excel calculated that result multiple times. LET allows you to call the expression by name and for Excel to calculate it once.
  • Easy Reading and Composition No more having to remember what a specific range/cell reference referred to, what your calculation was doing or copy/pasting the same expression. With the ability to declare and name variables, you can give meaningful context to yourself and consumers of your formula.

The is available only in relatively recent Excel versions, but not limited to Excel 365 which has functions like TEXTAFTER/TEXTBEFORE (see below).

Back to the formulas

Worksheet

=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))+LEN("]"),LEN(CELL("filename")))

Directory path

=LEFT(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-LEN("[")-LEN("/"))

Workbook filename

=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))+LEN("["),FIND("/",SUBSTITUTE(CELL("filename"),"]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))/LEN("]")))-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-LEN("["))

Workbook name

=MID(CELL("filename"),FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))+LEN("["),FIND("/",SUBSTITUTE(CELL("filename"),".xlsx]","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),".xlsx]","")))/LEN(".xlsx]")))-FIND("/",SUBSTITUTE(CELL("filename"),"[","/",(LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"[","")))/LEN("[")))-LEN("["))

More text related formulas for my link archive

The ones marked with * were mostly introduced together with TEXTAFTER, [Wayback/Archive] New Excel Functions (2022-03-16). The others can be used in older Excel versions too.

It is (for me most interesting) subset of [Wayback/Archive] Text functions (reference) – Microsoft Support.

Queries

–jeroen

This entry was posted on 2026/04/02 at 12:00 and is filed under Conference Topics, Conferences, Development, Event, Excel, Office, Power User, Scripting, SocialMedia, Software Development, Twitter. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

«