[RESOLVED] Find specific data in sheet name

[VBAhack]

If the name always has 3 parts separated by a single space between the parts, the following should work:

Dim Array1() As String
    Dim shName As String
    shName = "XXX YYY ZZZ"
    Array1 = Split(shName, Chr$(32))
    Debug.Print Array1(2) 'contains ZZZ

Here's another option:

Dim shName As String, Name2 As String, Name3 As String
    shName = "XXX YYY ZZZ"
    Name2 = Mid$(shName, 1 + InStr(shName, Chr$(32)))
    Name3 = Mid$(Name2, 1 + InStr(Name2, Chr$(32)))
    Debug.Print Name3

Option #3:

Dim str1 As String
    str1 = "XXX YYY ZZZ"
    Debug.Print Mid(str1, WorksheetFunction.Find(Chr$(32), str1, 1 + InStr(str1, Chr$(32)) + 1))

[billhuard]

Thanks a lot VBAhack

really helpful what you gave me