Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-24
9
Medium Priority
?
82 Views
Last Modified: 2016-10-24
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
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 41857134
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41857181
strExtension = Split("file.of.mine.txt", ".")(UBound(Split("file.of.mine.txt", ".")))
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41857192
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.

 

Author Comment

by:rayluvs
ID: 41857240
The formula worked excellently separating the extension, but how do modify the formula to identify the last repeated period (".")?
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41857241
I don't understand. What do you mean by "identify"?
0
 

Author Comment

by:rayluvs
ID: 41857256
example,

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

Expert Comment

by:aikimark
ID: 41857263
@rayluvs

Have you tested the other comments?
0
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 2000 total points
ID: 41857278
If you want the position of the last .
=SEARCH("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))

Open in new window

0
 

Author Comment

by:rayluvs
ID: 41857289
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question