Results 1 to 5 of 5
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Shorten text in a field (2003)

    I have many records in a field that are file paths like the following:
    file:///C:/Documents and Settings/HP_Owner/My Documents/My eBooks/My Pictures/abc&000/b06.jpg
    The length of each record may differ. I want to automatically remove everything in the string (path), except the file name. In the example, I want to keep only 06.jpg. Basically, can I use a function to remove every character from the left to right, up to and including the rightmost backslash ()? Thanks!
    JimmyW
    Helena, MT

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shorten text in a field (2003)

    Say that the text is in A1. The following formula entered as an array formula (confirm with Ctrl+Shift+Enter) will return the file name:

    =MID(A1,MAX((MID(A1,ROW(1:200),1)="/")*ROW(1:200))+1,100)

    200 is an arbitrary number larger than the length of the longest path, and 100 is an arbitrary number larger than the length of the longest file name. If the path contains backslashes, use "" instead of "/"

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Shorten text in a field (2003)

    Thanks, Hans. I pasted your formula into B1, thinking that it would operate on the path string in A1 and return the file name. Instead, it returned the entire path just as it appeared in A1. The paths use forward "/" slashes.
    JimmyW
    Helena, MT

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Shorten text in a field (2003)

    Did you make sure to enter the formula as an array formula? I.e., confirm with Ctrl+Shift+Enter instead of just Enter?

  5. #5
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Shorten text in a field (2003)

    Well, I found a way to accomplish this, albeit in a less scientific manner. As most of the paths were the same to a point, I did a few iterations of Find & Replace (with "nothing") and got rid of all but the file names. Again, I appreciate your help very much!
    JimmyW
    Helena, MT

Posting Permissions

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