Andreamary
asked on
Revising TRIM formula to also remove file extension
I am currently using the following formula to extract the filename from a file path:
Filepath:
C:\Program Files\xxxx\zzzz\tttt\iiiii iii\ooooo\ wwwwww\YYB G-POP-7E.a i
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
Filepath:
C:\Program Files\xxxx\zzzz\tttt\iiiii
Formula:
=TRIM(RIGHT(SUBSTITUTE(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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! I'm using Excel 2010, so the Excel 2007 version did the trick. Thanks so much...
=TRIM(RIGHT(SUBSTITUTE(LEF
regards, barry