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

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.
0
rayluvs
Asked:
rayluvs
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
aikimarkCommented:
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
0
 
Martin LissOlder than dirtCommented:
strExtension = Split("file.of.mine.txt", ".")(UBound(Split("file.of.mine.txt", ".")))
0
 
Rgonzo1971Commented:
Hi,

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

Open in new window

Regards
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
rayluvsAuthor Commented:
The formula worked excellently separating the extension, but how do modify the formula to identify the last repeated period (".")?
0
 
Martin LissOlder than dirtCommented:
I don't understand. What do you mean by "identify"?
0
 
rayluvsAuthor Commented:
example,

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

Have you tested the other comments?
0
 
Rgonzo1971Commented:
If you want the position of the last .
=SEARCH("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))

Open in new window

0
 
rayluvsAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now