Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,549
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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))

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default 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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding a position in a Cell using a formula jxbeeman Excel Discussion (Misc queries) 11 May 30th 09 02:40 AM
Finding the position of the i-th non blank cell in a vertical range vsoler Excel Worksheet Functions 6 September 11th 08 06:59 AM
Finding Column Position Brian Mann Excel Discussion (Misc queries) 1 September 22nd 05 09:26 AM
Period to Period percentage change? cs120 Excel Discussion (Misc queries) 1 September 18th 05 12:05 PM
Finding number of particular days in a period girlfriend in school Excel Worksheet Functions 0 July 13th 05 06:36 PM


All times are GMT +1. The time now is 08:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"