Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Change tab name - delete 3 characters

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Location
    Nesodden, Norway
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change tab name - delete 3 characters

    Hi


    I have a excel sheet with tabs named (short weekday in norwegian-date) like:
    M-1.1
    T-2.1
    O-3.1
    To-4.1
    F-5.1
    etc to:
    To-31.12

    Is there a easy way to remove the letters and the hyphen so result will be:
    1.1
    2.1
    3.1
    4.1
    5.1
    _____
    TorrO

  2. #2
    Board Regular
    Join Date
    Dec 2008
    Location
    Worcester, UK
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change tab name - delete 3 characters

    Loop through your sheets and hit them with this..

    Code:
    ActiveSheet.Name = Mid(ActiveSheet.Name, InStr(1, ActiveSheet.Name, "-") + 1, Len(ActiveSheet.Name) - InStr(1, ActiveSheet.Name, "-"))
    Edited to Add. If you weren't sure on the looping bit..

    Code:
    Sub test()
    
    
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    With ws
    .Activate
    ActiveSheet.Name = Mid(ActiveSheet.Name, InStr(1, ActiveSheet.Name, "-") + 1, Len(ActiveSheet.Name) - InStr(1, ActiveSheet.Name, "-"))
    End With
    Next
    
    
    End Sub
    Last edited by slinky; Jun 8th, 2015 at 08:07 AM.
    Avant-garde Excelista

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Location
    Nesodden, Norway
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change tab name - delete 3 characters

    So super easy, thanks slinky!!!
    _____
    TorrO

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Change tab name - delete 3 characters

    Just another option:

    Code:
    Sub M()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Name = Split(ws.Name, "-")(1)
    Next ws
    End Sub
    [code]your code[/code]

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Location
    Worcester, UK
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change tab name - delete 3 characters

    No worries. There's probably a slightly more efficient way of doing it, but that works well enough for me!
    Avant-garde Excelista

  6. #6
    Board Regular
    Join Date
    Dec 2008
    Location
    Worcester, UK
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change tab name - delete 3 characters

    Quote Originally Posted by FormR View Post
    Just another option:

    Code:
    Sub M()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Name = Split(ws.Name, "-")(1)
    Next ws
    End Sub
    I like that! I'll be copying that one to my bible if you don't mind..
    Avant-garde Excelista

  7. #7
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Change tab name - delete 3 characters

    Quote Originally Posted by slinky View Post
    if you don't mind..
    Of course not, please feel free to use and abuse
    [code]your code[/code]

  8. #8
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,589
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Change tab name - delete 3 characters

    TorrO,

    Here is another macro solution for you to consider that checks each worksheet name to see if it contains the single - character.

    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Code:
    Sub ChangeSheetNames()
    ' hiker95, 06/08/2015, ME859891
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
      If InStr(ws.Name, "-") Then
        ws.Name = Split(ws.Name, "-")(1)
      End If
    Next ws
    End Sub
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

    Then run the ChangeSheetNames macro.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  9. #9
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Change tab name - delete 3 characters

    Select the column, press Ctrl-H and replace *- (asterisk dash) with nothing but I do not know how the remaining part (1.1, 2.1 etc) will be displayed in your date-system.

  10. #10
    Board Regular
    Join Date
    Dec 2008
    Location
    Worcester, UK
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change tab name - delete 3 characters

    Quote Originally Posted by István Hirsch View Post
    Select the column, press Ctrl-H and replace *- (asterisk dash) with nothing but I do not know how the remaining part (1.1, 2.1 etc) will be displayed in your date-system.
    That's not going to work to well for Worksheet names though?
    Avant-garde Excelista

Some videos you may like

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
  •