finding the right character length

[mario92]

Hi,

Im having trouble creating code to read off the name of my excel tabs. the names always start w/ 2 blank spaces and then they can be 2, 3, or 4 letters after it and then followed by a space. I need the code to discern if the name of the tab has a certain amount of letters to continue my code.

right now i have this and a few on error goto(s) to try to find it out such as tname = Mid(SheetName, 3, 3) or tname = Mid(SheetName, 3, 2)

SheetName = ActiveWorkbook.Worksheets(I).Name
tname = Mid(SheetName, 3, 4)
ActiveWorkbook.Worksheets(I).Activate


im sure there is a better way to do this probably with if statements or a switch. can anyone provide any suggestions?

 

[Joe4]

Try this:

    SheetName = ActiveWorkbook.Worksheets(I).Name
    
    Dim arr() As String
    arr = Split(Trim(SheetName), " ")
    tname = arr(0)

 

[DanteAmor]

In the n you have the number of characters

Sub countletters()
  Dim n As Long, i As Long
  
  For i = 1 To Sheets.Count
    n = Len(Trim(Sheets(i).Name))
    MsgBox n
  Next
End Sub

 

[mario92]

awesome thanks it worked

 

[mario92]

awesome thanks it worked

I did also see your previous post on making sure i tell you it helped by hitting the checkmark

 

[Joe4]

You are welcome.
Glad we were able to help!