asked on

Function or formula to identify the last repeated character in a cell in Excell

We have a list of files in an excel and want to separate their file extension. We are using a combination of commands like search(), len(), left(), right(). However some files has various periods in their name (for example, file.of.mine.txt), thus making it difficult to set the 3 character of the extension. So we thought if we can identify the last period we can calculate the exact 3 or 2 or 5 extension that a file may have (for example, filename.text or filename.manifext).

please advice.

aikimark

You can use instrrev() to find the last instance of a char/string within a target string.
Example:

strFilename = "file.of.mine.txt"
strBasename = Left(strFilename, Instrrev(strFilename, ".") -1)
strExtension = Mid(strFilename, Instrrev(strFilename, ".") +1)

Open in new window

Note: You can also get this information with a FileSystemObject