This is a fairly simple problem in the latest version of Excel, which provides theTEXTAFTER functionand theTEXTBEFORE function.
Both options rely on theCELL functionto get a full path to the current workbook.
Read below for details.
The challenge then becomes how best to extract just the text between the square brackets from the path.
The best way to do this depends on what Excel version you have.
If you have the latest version of Excel, you should use TEXTAFTER and TEXTBEFORE functions.
Otherwise, you’ve got the option to use the MID and FIND functions as explained below.
Notice the CELL function is called 4 times!
The cell reference is arbitrary and can be any cell in the worksheet.
Notice the workbook name appears enclosed in square brackets.
We then add 1 because we want to extract text starting one characterafterthe “[”.
We know where we want to start (20) and where to end (32).
However, while the MID function takesstart_numas the starting position, there is no equivalentend_num.
Instead, MID takesnum_chars, the number of characters to extract.
This is the length of the workbook name in characters.
The jot down of information to be returned is specified asinfo_type.
For example, =MID(“apple”,2,3) returns “ppl”.
When the text is not found, FIND returns a #VALUE error.
