Finding position of last period in filename
I have a large spreadsheet of filenames. I'm trying to break the
names into Filename and File extension using LEFT and RIGHT functions. I'm having trouble getting some of these names because some of the filenames contain more than one period, eg., This.is.the.filename.DOC is in Col A. Can someone suggest a function that would separate both parts? This.is.the.filename in Col B DOC in Col C. Thanks in advance. |
Finding position of last period in filename
Three functions (filename in A5)... =SUBSTITUTE(RIGHT(SUBSTITUTE(A5,".",REPT(".",99)), 99),".","") -- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (Extras for Excel add-in: convenience built-in) "Jack Deuce" wrote in message ... I have a large spreadsheet of filenames. I'm trying to break the names into Filename and File extension using LEFT and RIGHT functions. I'm having trouble getting some of these names because some of the filenames contain more than one period, eg., This.is.the.filename.DOC is in Col A. Can someone suggest a function that would separate both parts? This.is.the.filename in Col B DOC in Col C. Thanks in advance. |
Finding position of last period in filename
On Mon, 28 Mar 2011 10:29:43 -0500, Jack Deuce wrote:
I have a large spreadsheet of filenames. I'm trying to break the names into Filename and File extension using LEFT and RIGHT functions. I'm having trouble getting some of these names because some of the filenames contain more than one period, eg., This.is.the.filename.DOC is in Col A. Can someone suggest a function that would separate both parts? This.is.the.filename in Col B DOC in Col C. Thanks in advance. B1: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LE N(A1)-LEN(SUBSTITUTE(A1,".",""))))-1) C1: =TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)) |
Finding position of last period in filename
B1:
=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LE N(A1)-LEN(SUBSTITUTE(A1,".",""))))-1) C1: =TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)) Perhaps, since we know what C1 will contain, this simpler formula for B1... B1: =SUBSTITUTE(A1,"."&C1,"") Rick |
Finding position of last period in filename
On Mon, 28 Mar 2011 22:37:18 -0400, "Rick Rothstein" wrote:
B1: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LE N(A1)-LEN(SUBSTITUTE(A1,".",""))))-1) C1: =TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)) Perhaps, since we know what C1 will contain, this simpler formula for B1... B1: =SUBSTITUTE(A1,"."&C1,"") Rick I don't like that because it fails if the suffix happens to also exist within the first part of the extract. e.g.: A1: This.is.the.DOCument.filename.DOC |
Finding position of last period in filename
On Tue, 29 Mar 2011 07:14:53 -0400, Ron Rosenfeld
wrote: On Mon, 28 Mar 2011 22:37:18 -0400, "Rick Rothstein" wrote: B1: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LE N(A1)-LEN(SUBSTITUTE(A1,".",""))))-1) C1: =TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)) Perhaps, since we know what C1 will contain, this simpler formula for B1... B1: =SUBSTITUTE(A1,"."&C1,"") Rick I don't like that because it fails if the suffix happens to also exist within the first part of the extract. e.g.: A1: This.is.the.DOCument.filename.DOC Both solutions work perfectly. Thanks again. |
Finding position of last period in filename
On Tue, 29 Mar 2011 08:15:16 -0500, Jack Deuce wrote:
Both solutions work perfectly. Thanks again. Glad to help. Thanks for the feedback. |
All times are GMT +1. The time now is 10:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com