Simpler way to get part of sheet tab name
Simpler way to get part of sheet tab name
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Simpler way to get part of sheet tab name

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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)

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,779
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


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






  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    "Have a good time......all the time"
    Ian Mac

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      


    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com