Formule ou VBA pour récupérer le niveau N d'un nom de dossier.

Sub arbo()
nbniveaux = InputBox("entrez le nombre de niveaux")
monTablo = Split(ThisWorkbook.Path, "\")
If Not nbniveaux > UBound(monTablo) Then
    For niv = (UBound(monTablo) + 1 - nbniveaux) To UBound(monTablo)
        chem = chem & monTablo(niv) & "\"
    Next niv
chem = Mid(chem, 1, Len(chem) - 1)
End If
End Sub

Function Niveau(Rg As Range, N As Integer)
Niveau = Split(Rg, "\")(N)
End Function

Function Niveau(Rg As Range, N As Integer)
Dim T As Variant
T = Split(Rg, "\")
If N > UBound(T) Then N = UBound(T)
ReDim Preserve T(0 To N)
Niveau = Join(T, "\") & "\"
End Function

Public cell, nbniveaux
Sub arbo()
nbniveaux = InputBox("entrez le nombre de niveaux")
t1 = Timer
'monTablo = Split(ThisWorkbook.Path, "\")
For Each cell In Selection
extrait_Niveau
Next
MsgBox Timer - t1
End Sub
Sub extrait_Niveau()
Dim chem
'cell.Select
montablo = Split(cell.Value, "\")
If Not nbniveaux > UBound(montablo) Then
    For niv = (UBound(montablo) + 1 - nbniveaux) To UBound(montablo)
        chem = chem & montablo(niv) & "\"
    Next niv
chem = Mid(chem, 1, Len(chem) - 1)
cell.Offset(0, 1) = chem
End If
montablo = ""
End Sub

Sub arbo3()
nbniveaux = InputBox("entrez le nombre de niveaux")
t1 = Timer
For Each cell In Selection
   cell.Offset(0, 1).Value = Niveau(cell, nbniveaux)
Next
MsgBox Timer - t1
End Sub

Private Function Niveau(ByVal Plg As Range, ByVal Num As Long) As String
Dim T, TReport(), i&, J&
If Plg.Count > 1 Or Num < 1 Or InStr(Plg.Value, "\") = 0 Then Exit Function
T = Split(Plg.Value, "\")
If Num > UBound(T) - 1 Then Exit Function
ReDim TReport(Num - 1)
J = UBound(TReport)
For i = UBound(T) To (UBound(T) - (Num - 1)) Step -1
    TReport(J) = T(i)
    J = J - 1
Next i
Niveau = Join(TReport, "\")
End Function

Function Niveau(Rg As Range, N As Integer)
Dim T As Variant
T = Split(Rg, "\")
If N >= UBound(T) Then Niveau = "": Exit Function
ReDim Preserve T(0 To N)
Niveau = Replace(Rg, Join(T, "\") & "\", "")
End Function