Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formula for Filename (XL 97)

    Need a formula to lop off the filetype from the end of a file spec. Must work for all these

    <table border=1><td>Before</td><td>After</td><tr><td>a ver 1.2.csv</td><td>a ver 1.2</td><td>x.y.1.z</td><td>x.y.1</td></table>

    No VBA please, I can do that. TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula for Filename (XL 97)

    Assuming that the cell below "before" is A2, enter into B2:

    =LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),LE N(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)

    Steve

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula for Filename (XL 97)

    Steve, very cool formula. I figured it would be an array formula. Never knew Substitute's Instance Number could be so useful! Thanks again! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •