Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Sacramento, California, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel file name (Ver 2000/SP3)

    ='server01dept-mcsContract Status GraphsRancho Seco[4500000322 Envirocare of Utah Inc Contract Graphs.xls]Data'!$B$2

    I would like to know how to extract just the file name [ ] and paste it to a cell for use in different cell.

    thanks

  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: excel file name (Ver 2000/SP3)

    I assume that this is a cell formula and not a string value. This will require a custom function. Add this to a module:
    <pre>Option Explicit
    Function ExtractFilename(rCell As Range)
    Dim iStart As Integer
    Dim iLen As String
    Dim sFormula As String
    sFormula = rCell.cells(1).Formula
    iStart = InStr(sFormula, "[") + 1
    iLen = InStr(sFormula, "]") - iStart
    ExtractFilename = Mid(sFormula, iStart, iLen)
    End Function</pre>


    If the formula is in cell A1:
    Add this into a cell to get the filename:
    =ExtractFilename(A1)

    Steve

Posting Permissions

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