Finding position of last period in filename

[Jack Deuce]

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.

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Ron Rosenfeld]

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),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1)

C1: =TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))

 

[Rick Rothstein]

B1:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LEN(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

 

[Ron Rosenfeld]

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

 

[Jack Deuce]

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.

 

[Ron Rosenfeld]

Both solutions work perfectly. Thanks again.

Glad to help. Thanks for the feedback.