Simpler way to get part of sheet tab name

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
Sheet (tab) names consist of a varying number of alpha characters plus a number. The number is always the rightmost character. I want to return the sheet name without the rightmost character to use in various other ways such as VLOOKUP functions. The formula below works fine but is there a simpler way to do it?

And is there much to be gained (besides simpler reading) by naming this formula and using the name instead of the whole formula within other formulas?

=LEFT(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),LEN(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))))-1)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
On 2002-04-05 09:16, pilot wrote:
Sheet (tab) names consist of a varying number of alpha characters plus a number. The number is always the rightmost character. I want to return the sheet name without the rightmost character to use in various other ways such as VLOOKUP functions. The formula below works fine but is there a simpler way to do it?

And is there much to be gained (besides simpler reading) by naming this formula and using the name instead of the whole formula within other formulas?

=LEFT(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),LEN(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))))-1)

Pilot,

An important part in the CELL("filename") bit is missing: It should be something like CELL("filename",A1), where A1 has no other significance then forcing the filename to be anchored to the sheet in which it's entered in some cell. Otherwise, it will produce unexpected outcome.

You can also make of it a named formula:

Activate Insert|Name|Define.
Enter GetSheetName in the Names in Workbook box.
Enter in the Refers to box:

=LEFT(RIGHT(CELL("filename",!$A$1),LEN(CELL("filename",!$A$1))-SEARCH("]",CELL("filename",!$A$1))),LEN(RIGHT(CELL("filename",!$A$1),LEN(CELL("filename",!$A$1))-SEARCH("]",CELL("filename",!$A$1))))-1)

Try in different sheets in your WB by entering in some cell:

=GetSheetName

Aladin
 
Upvote 0
answer is probably a UDF; however,
I would first do the following:

The following is just an enhancement to your approach.

1. correct your Sheet Name formula so it shows the name for the particular sheet

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

If the above is in say A10, then in another cell for clarity =LEFT(A10,LEN(A10)-1).
 
Upvote 0
Excellent, Aladin, you have the fix to a problem I posted a couple weeks ago but never got an answer. Without the $A$1 anchor, the formula does not update with info about the newly selected sheet until a Calculate had run. I worked around that problem by adding code to force the calculate upon changing sheets. I like this much better.
 
Upvote 0
On 2002-04-05 10:11, pilot wrote:
Excellent, Aladin, you have the fix to a problem I posted a couple weeks ago but never got an answer. Without the $A$1 anchor, the formula does not update with info about the newly selected sheet until a Calculate had run. I worked around that problem by adding code to force the calculate upon changing sheets. I like this much better.

A tip really! I had help on a similar question for recently and I've noticed a few people have started throwing it at this board. If you didn't get answered or before you ask the question, use the now excellent search facility on this board, you'll often find the answer e.g. I just typed "CELL filename" and got what I think you've been looking for.
I must admit when I had the same problem I didn't search because I thought surely no one has asked this before. more the fool me!
 
Upvote 0
Sub WorksheetName()

ActiveCell.Value = ActiveSheet.Name
End Sub

OP could probably extend this to delete the last character.

If desired, One can attach this to a custom icon.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top