Simpler way to get part of sheet tab name

[pilot]

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)

[Aladin Akyurek]

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

[Dave Patton]

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).

[pilot]

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.

[Ian Mac]

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!

[Dave Patton]

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.