« Extract username and domain name from email ID using Formulas | How to extract 2nd, 3rd, 4th & 5th Instance from VLOOKUP? » |
Hi there,
Have you come across this situation in your work. This is really amazing trick to extract the active sheet name to a cell. The formula goes like this.
=mid(cell("filename",a1),find("]",cell("filename",a1))+1,111)
dynamic formula, what if cell A1 deletes either by Row or Column:
=mid(cell("filename"),find("]",cell("filename")+1,111)
EXPLANATION:
...
CELL("filename",a1) gives the complete path of the file name either it is on local drive or from external source.
FIND("]",CELL("filename",a1))+1 gives the number of characters present to reach the character "]"
111 is the character selection from the filename path.
Mix together gives the SheetName of the active sheet.
This formula is very useful because it gets updated when ever the sheet name gets changed by the user.
Note: Make sure the above formula works on the saved worksheet. Otherwise, you will get a #VALUE! error.
That's it. Magic works!!!
Here is an another approach:
=TRIM(RIGHT(SUBSTITUTE(CELL("filename"),"]",REPT(" ",31)),31))
DO YOU USE FILENAME?
You can share your experience and findings about a function "filename" using the below comments section. If you like this post please do share it on your social networking sites by clicking on the icons placed around the blog.
If you want to support me, REGISTER here for free. Yes, Its absolutely free.
SOME IMPORTANT LINKS:
Count Worksheets in a Workbook
Conditional Formatting on your finger tips
Thanks,
You must be logged in to see the comments. Log in now!
If you have no account yet, you can register now! (It only takes a few seconds)
Recent comments