Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  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

    Formula help: is =Right right?

    In A1, I have a text string (a file path) like S:\WindowsAdvantage\Mine\johnnie\New Files\yours\file (22).JPG. The other records in the field are similar, but the paths vary in length and depth. In B1, I want to return only the file name, or file (22).JPG in the example. So, I tried to use the Right function to return everything after the last backslash (some records have more or less backslashes) in A1: =RIGHT(A3,FIND("\",A3)*50) Doesn't work. Thanks.
    JimmyW
    Helena, MT

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    You can either use one of the add-ins which has a function to reverse a string, or use something clumsy like this:

    Code:
    =MID(A1,FIND("^",SUBSTITUTE(A1,"\","^",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))+1,LEN(A1))
    Instead of the caret (^) you can use any other character which cannot be used in a Windows filepath.

    Its a much simpler proposition directly in VBA or indirectly with a UDF, but the above seems to work as intended.

  3. The Following User Says Thank You to MartinM For This Useful Post:

    Jimmy-W (2012-11-09)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jimmy,

    If you're not adverse to VBA code you could use this:
    Code:
    Option Explicit
    
    Function zGetFileNameExt(zFullPath As String) As String
    
       Dim vResults As Variant
       
       vResults = Split(zFullPath, "\")
       
       zGetFileNameExt = vResults(UBound(vResults))
       
    End Function
    Then it's easy to code the individual entries since you can do the 1st one and then drag it down.

    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Jimmy-W (2012-11-09)

  6. #4
    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
    Thanks very much. I know that I'm a little challenged when it comes to VBA, but I found and entered the folowing in a new module:

    Function Reverse(Text As String) As String
    Dim i As Integer
    Dim StrNew As String
    Dim strOld As String
    strOld = Trim(Text)
    For i = 1 To Len(strOld)
    StrNew = Mid(strOld, i, 1) & StrNew
    Next i
    Reverse = StrNew
    End Function

    I'm doing something wrong, no doubt, but I get a #NAME? error when I enter =reverse(a2) in B2.
    JimmyW
    Helena, MT

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Function worked like a charm for me. Incorporated into a cell function this worked:

    =reverse(LEFT(reverse(A1),FIND("\",reverse(A1),1)-1))

    Code coud even be shortened to:
    Function Reverse(Text As String) As String
    Dim i As Integer
    Dim strOld As String
    strOld = Trim(Text)
    For i = 1 To Len(strOld)
    Reverse = Mid(strOld, i, 1) & Reverse
    Next i
    End Function

    HTH,
    Maud
    Last edited by Maudibe; 2012-11-08 at 17:39.

  8. #6
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi Jimmy-W

    Try: =TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

  9. The Following User Says Thank You to Kevin@Radstock For This Useful Post:

    Jimmy-W (2012-11-09)

  10. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Kevin, that's very neat.
    PS I'm having dinner in Emborough tomorrow night !!

  11. #8
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Thanks for the feed back Martin. Long way to go to get something to eat from Fukuoka! Not a lot in Emborough. Enjoy your meal.
    Last edited by Kevin@Radstock; 2012-11-09 at 09:07.

  12. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Way, way off-topic I know, but we're going to the Old Down Inn at Emborough - it only takes 28 hours in total from Fukuoka, but it had better be good !

  13. #10
    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
    I also just discovered that a simple Find & Replace of Find= *\ Replace <empty> seems to work.
    JimmyW
    Helena, MT

  14. #11
    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
    So, I copied RetiredGeek's code into a new module, but it does not appear when I try to run it - I had the same issue with the other VBA suggestions, so it's got to be user error!
    JimmyW
    Helena, MT

  15. #12
    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
    Quote Originally Posted by Jimmy-W View Post
    So, I copied RetiredGeek's code into a new module, but it does not appear when I try to run it - I had the same issue with the other VBA suggestions, so it's got to be user error!
    They are not macros to run (via Macro - run) but user functions, akin to use like the XL functions. RetiredGeek's macro is called as he shows by putting a formula in a cell like:
    =zGetFileNameExt(A1)

    Where in cell A1 you have the full path of the filename.

    Steve

  16. The Following User Says Thank You to sdckapr For This Useful Post:

    Jimmy-W (2012-11-09)

  17. #13
    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
    I should have explained that I also entered the formula, =zGetFileNameExt(A1), and Excel returned the #NAME? error. The same thing with another suggested formula, as I described in message #5 above. Yet, it seems as though others don't have this problem.
    JimmyW
    Helena, MT

  18. #14
    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
    Where did you put the code? It needs to be in a module in the workbook you are calling it from. If you want to call it from a different (yet open) workbook, you need to add the workbook name to the formula (for example):
    =Personal.xls!zGetFileNameExt(A1)

    The #Name? error means it can't find the function

    Steve

  19. #15
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Another simple solution that I have used before is thus:

    Sub filename()


    Range("B1") = Right(Range("A1"), Len(Range("A1")) - InStrRev(Range("A1"), "\", -1))

    End Sub



    I placed your file path in cell A1 and this code returns the "filename" into cell B1
    This code uses the "In String Reverse" function to find the last occurrance of the search item, in this case the "\" character.

    I then find the file name by using the "right" function and telling it to get the right most number of characters by subtracting the position of the last character from the length of the string.
    Hope you can understand this code.
    Regards
    Brian.

  20. The Following User Says Thank You to BRoby For This Useful Post:

    Maudibe (2012-11-10)

Page 1 of 2 12 LastLast

Posting Permissions

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