[RESOLVED] Find specific data in sheet name

Hi guys,

now I need to find specific data in sheet name
all sheets name in the workbook are on the same pattern.
Here is the patern: "XXX YYY ZZZ"

All X, Y and Z are variable in lenght and they are seperate with a space.

I need the value of Z but how to find it with a variable lenght????

Thanks

Here is my code with where I need to introduce de tricky "last part of the sheet name"

VB Code:

With Workbooks(lcFileName)
            For lnSheetId = 2 To .Sheets.Count
            ' sheet 1 is Dept... now starting from sheet 2 check if it is Ss-Dept
                If Not IsNumeric(.Sheets(lnSheetId).Name) And Len(Trim(.Sheets(lnSheetId).Name)) > 3 Then
                    SsDept = .Sheets(lnSheetId).Name
 
                    'Need to find the specific data HERE
                    'Need to find the specific data HERE
 
 
                    SsDeptIndex = Workbooks(fullname).Sheets(tsNameFile & " " & SsDeptName).Index - 1 'marche pas
                    
                    'if sheet already exists in the Department Workbook
                    If SheetExists(fullname, SsDept) = True Then
                        Workbooks(fullname).Sheets(SsDept).Delete
                    End If
                    .Sheets(SsDept).Copy After:=Workbooks(fullname).Sheets(SsDeptIndex)
                End If
            Next lnSheetId
        End With

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

VB Code:

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:

VB Code:

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:

VB Code:

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

Thanks a lot VBAhack

really helpful what you gave me