?
Solved

Revising TRIM formula to also remove file extension

Posted on 2014-03-17
4
Medium Priority
?
1,089 Views
Last Modified: 2014-03-19
I am currently using the following formula to extract the filename from a file path:

Filepath:
C:\Program Files\xxxx\zzzz\tttt\iiiiiiii\ooooo\wwwwww\YYBG-POP-7E.ai

Formula:
=TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",LEN(A2))),LEN(A2)))

Results:
YYBG-POP-7E.ai
YOBP-QRT-4G.doc

I would like to revise the formula so it also removes the file extensions, which are either 2 or 3 character extensions, so that the results would be:

YYBG-POP-7E
YOBP-QRT-4G

Thanks!
Andrea
0
Comment
Question by:Andreamary
[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
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 39935283
Try this version

=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(".",A2)-1),"\",REPT(" ",LEN(A2))),LEN(A2)))

regards, barry
0
 

Author Comment

by:Andreamary
ID: 39938203
Hi Barry,

It does the trick! The only tweak, if possible, is to hide the #VALUE if the cell in Column A is blank. Is that feasible?

Thanks,
Andrea
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 39938433
Assuming you are using Excel 2007 or later you can just wrap the formula in IFERROR like this:

=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(".",A2)-1),"\",REPT(" ",LEN(A2))),LEN(A2))),"")

that will return a blank instead of an error.

In earlier versions you could use a simple IF like

=IF(A2="","",TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(".",A2)-1),"\",REPT(" ",LEN(A2))),LEN(A2))))

That specifically returns a blank if A2 is blank

regards, barry
0
 

Author Closing Comment

by:Andreamary
ID: 39940500
Perfect! I'm using Excel 2010, so the Excel 2007 version did the trick. Thanks so much...
0

Featured Post

ATEN's HDBaseT Presentation at InfoComm 2017

Hear ATEN Product Manager YT Liang review HDBaseT technology, highlighting ATEN’s latest solutions as they relate to real-world applications during her presentation at the HDBaseT booth at InfoComm 2017.

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…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

801 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