• Status: Solved
• Priority: Medium
• Security: Public
• Views: 92

# 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).

0
rayluvs
• 3
• 2
• 2
• +1
2 Solutions

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

Note: You can also get this information with a FileSystemObject
0

Older than dirtCommented:
strExtension = Split("file.of.mine.txt", ".")(UBound(Split("file.of.mine.txt", ".")))
0

Commented:
Hi,

pls try as a formula
``````=RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))
``````
Regards
0

Author Commented:
The formula worked excellently separating the extension, but how do modify the formula to identify the last repeated period (".")?
0

Older than dirtCommented:
I don't understand. What do you mean by "identify"?
0

Author Commented:
example,

in file.of.mine.txt, the last "." is in the 13 position.  We want to identify the last ".".
0

Commented:
@rayluvs

Have you tested the other comments?
0

Commented:
If you want the position of the last .
``````=SEARCH("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))
``````
0

Author Commented:
The other comments don't work in the cell, we want work directly on Excel cell.

Thanx Rg worked!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.