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

1. ## 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. ## 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.``

3. ## 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. ## 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. ## 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. ## 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.

7. ## 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. ## Re: Need a complicated formula to display part of current workbook name in a cell

Don't you trust me?

9. ## 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

10. ## 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. ## 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. ## 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!

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

#### 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