+ Reply to Thread
Results 1 to 12 of 12

Need a complicated formula to display part of current workbook name in a cell

  1. #1
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Need a complicated formula to display part of current workbook name in a cell

    Hi all.

    I want to insert the name of the current workbook into a cell in my workbook. I got this formula from the Microsoft website, which gives the name of the workbook including the full path:

    =CELL("filename")

    Then I got this, which takes me a step closer, and gives me just the name of the workbook, including the extension:

    =((MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)))

    but what I really need is the name of the current workbook, minus the ".xls" extension from the right-hand side, but also minus 25 characters from the left-hand side. Is this possible? I'm guessing I would have to use the LEN function somewhere (twice) in combination with LEFT and RIGHT, but I can't work it out.

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Need a complicated formula to display part of current workbook name in a cell

    Thsi will remove the .xls part
    Please Login or Register  to view this content.
    this will remove first 25 characters of that result..
    Please Login or Register  to view this content.
    Last edited by Ace_XL; 07-06-2013 at 05:38 AM.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Need a complicated formula to display part of current workbook name in a cell

    So would it be possible for me combine all of that into the existing formula? Or would that be stretching it? Maybe I could use a couple of hidden cells, and do it three steps at a time?

  4. #4
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Need a complicated formula to display part of current workbook name in a cell

    Ah ok, I see what you did there. It does work, but I have a problem. If I open another workbook in the same instance of Excel, it changes the filename to that of the other workbook, and therefore the contents of the cell. Is there a way I can make the cell formula only reference the current workbook?

  5. #5
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Need a complicated formula to display part of current workbook name in a cell

    Never mind, I found the solution online (to refer to the current cell with each "filename"). Thanks!

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need a complicated formula to display part of current workbook name in a cell

    Try this...

    =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND(".",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

    Assumes there will only be one "dot" in the file name.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Need a complicated formula to display part of current workbook name in a cell

    It's ok, it's working fine for me now, so I don't dare change it! But thanks anyway

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need a complicated formula to display part of current workbook name in a cell

    Don't you trust me?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Need a complicated formula to display part of current workbook name in a cell

    trust him, trust him...he's what you call...an expert
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Need a complicated formula to display part of current workbook name in a cell

    Of course I do! It's just that I don't dare change something if it's already working, because in my experience it inevitably breaks, and I forget how I had it when it worked! If I can have a fully working version of the project that I can put in a completely separate folder, THEN I can tinker with it!

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,878

    Re: Need a complicated formula to display part of current workbook name in a cell

    Make a copy of the formula (from the formula bar, not just copy/paste), then past it somewhere and try playing with it...and dont forget you can always UNDO

  12. #12
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Need a complicated formula to display part of current workbook name in a cell

    Tell you what, for now I'll compromise and make a copy of the formula, and then I can try it later. At the moment I'm trying to get some critical processes working properly, and I need to get that done first!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1