Find Last Character in Excel

From time to time in Excel, I find that I need to pull off a sub-string before or after the last instance of a character in some text.

Examples:

1. I have a list of fully-qualified filenames. I want to split the name putting the full path in one cell and just the filename in another cell.
2. I have a list of corporation names followed some number. I want to put the name (which could be made up of any number of words) in one cell and the number in another.

In order to do this, I need to find the last position that a specific character occurs in the source text. For the filenames, I need to find the last "\" or "/" character. For the corporate names, I need to find the last space character.

Excel does not have a SPLIT or FINDLAST formula. It does, however, have a SUBSTITUTE method that proves to be quite helpful.

With the original text in cell J2, here's the answer to get the text before the last space:

=LEFT(J2, FIND("*", SUBSTITUTE(J2, " ", "*", LEN(J2) - LEN(SUBSTITUTE(J2, " ", "")))) - 1)

And, here's the answer to get the text after the last space:

=RIGHT(J2, LEN(J2) - FIND("*", SUBSTITUTE(J2, " ", "*", LEN(J2) - LEN(SUBSTITUTE(J2, " ", "")))))