Excel: Extract a substring…starting from the end

Now there’s a whole heap of posts, forum answers and articles out there on how to find a substring in Excel by playing around with formulae. The problem that almost all of these have is that they assume you always want to start from the beginning of the string. But what if you need to find information from within the string instead?

Let’s take a simple example: you want to find the surname from a “firstname lastname” string. That’s easy, just look for the substring that comes after the first space using FIND and MID functions in combination and it’s job done, like this:

 =FIND(" ",fullString)

and:

=MID(fullString,start)

combined gives us:

=MID(fullString,FIND(" ",fullString)+1)

Obviously, we could also add some extra stuff to make it funkier, maybe add a TRIM function to remove any excess spaces in the result, or ensure we add the optional string length in the MID function and so on.

All good so far, but what if you are starting with a more complex string? This time, let’s suppose you have cunningly obtained the full path, including sheet name, of the current file, by using  the Excel CELL function below:

=CELL("filename",A1)

From this full path, you want to extract only the sheet name. The active sheet in this example is named “MySheet” and is located in the file C:FilesMyBook.xls.

So, the result having used the CELL formula above is “C:Files[MyBook.xls]MySheet

In order to get the sheet name we can always use:

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

Which will give us the desired result – “MySheet“.

That’s fine, but why don’t we just look for items from the end of the string, since we know that’s where we’ll find the information we want? All we need is an Excel-friendly version of the VBE InStrRev function and we’ll be ready to roll, as follows:

Function XLInStrRev(ByVal StringCheck As String, ByVal StringMatch As String, _
Optional ByVal Start As Long = -1, _
Optional ByVal VBCompareMethod As Long = vbBinaryCompare)
XLInStrRev = InStrRev(StringCheck, StringMatch, Start, VBCompareMethod)
End Function

Now we can use the formula:

'=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-XLInStrRev(CELL("filename",A1),"]"))

This is shorter (88 characters vs 112), neater and so very versatile.

Have a play, see what it can do and enjoy.