Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • 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 LissRetired ProgrammerCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rayluvsAuthor Commented:
The formula worked excellently separating the extension, but how do modify the formula to identify the last repeated period (".")?
0
 
Martin LissRetired ProgrammerCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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