Excel lastindex of a substring


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):


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


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:


Now we can use that to get the folder path:


Here's the folder path without the trailing \


And to get just the filename:


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:

posted @ 2018-04-25 06:41 emanlee 阅读(...) 评论(...) 编辑 收藏