Between Function?
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Between Function?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi everyone,

    Ive looked on the board and found similar problems, but nothing that quite matches mine.

    Im trying to find just the file name (no extension) from a directory returned from a macro. So for example, from the directory:

    s:FileServerFilesYear2002

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Not sure about the worksheet function (would have imagined some sort of array formula) but you can use VBA like this:-

    Code:
    Sub Test()
    Dim strFullPath As String, strFileName As String
    Dim lngCharLoop As Long
    
    strFullPath = "s:FileServerFilesYear20023-02Jones, Rob.xls"
    
    For lngCharLoop = Len(strFullPath) To 1 Step -1
        If Mid$(strFullPath, lngCharLoop, 1) = "" Then Exit For
    Next lngCharLoop
    
    strFileName = Right(strFullPath, Len(strFullPath) - lngCharLoop)
    MsgBox strFileName
    End Sub
    Hope it helps,
    D

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,748
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    [quote]
    On 2002-03-23 12:12, robfo0 wrote:
    Hi everyone,

    Ive looked on the board and found similar problems, but nothing that quite matches mine.

    Im trying to find just the file name (no extension) from a directory returned from a macro. So for example, from the directory:

    s:FileServerFilesYear2002

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks guys!

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [quote]
    On 2002-03-23 12:12, robfo0 wrote:
    Hi everyone,

    Ive looked on the board and found similar problems, but nothing that quite matches mine.

    Im trying to find just the file name (no extension) from a directory returned from a macro. So for example, from the directory:

    s:FileServerFilesYear2002

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi
    This code can also do the job:

    Function NameFromPath(strPath As String) As String
    Dim lngPos As Long
    Dim strPart As String
    lngPos = InStrRev(strPath, "") + 1
    If lngPos > 0 Then strPart = Mid(strPath, lngPos, Len(strPath) - lngPos - 3)
    NameFromPath = strPart
    End Function

    regards Tommy


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com