codice:
Private Function formatNumStringa(prms_Input As String, prml_NumChar As Long, _
    Optional prms_FormatChar As String = "0") As String

'###############################################################################
'# Parametri:                                                                  #
'#     prms_Input: Stringa da formattare                                       #
'#     prml_NumChar: Numero dei caratteri per i quali formattare               #
'#     prms_FormatChar: Carattere usato per formattare                         #
'# Valore restituito:                                                          #
'#     stringa che formatta i numeri in maniera che siano sia numericamente    #
'#     che alfabeticamente ordinati                                            #
'#   "10 - Padova Arcella" -> "0000000010 - Padova Arcella                     #
'###############################################################################

Dim a() As String
Dim i As Integer
Dim ls_temp As String
Dim ls_Str000 As String
Dim ll_numericiconsecutivi As Long

    ls_Str000 = String$(prml_NumChar, prms_FormatChar)
    ReDim a(0 To Len(prms_Input) + 1)
    
    For i = UBound(a) - 1 To LBound(a) + 1 Step -1
        a(i) = Mid$(prms_Input, i, 1)
        If IsNumeric(a(i)) Then
            ls_temp = a(i) * (10 ^ ll_numericiconsecutivi)
            ll_numericiconsecutivi = ll_numericiconsecutivi + 1
            a(i) = Format$(Val(ls_temp) + Val(a(i + 1)), ls_Str000)
            If (Val(a(i + 1))) > 0 Then
                a(i + 1) = ""
            End If
            If Replace(a(i + 1), prms_FormatChar, vbNullChar) = String$(prml_NumChar, vbNullChar) Then
                a(i + 1) = ""
            End If
        Else
            ll_numericiconsecutivi = 0
        End If
    Next i
    For i = LBound(a) + 1 To UBound(a)
        formatNumStringa = formatNumStringa + a(i)
    Next i
End Function

in pratica voglio evitare di fare il secondo ciclo devo trovare il modo di farne uno solo
p.s.:
non è detto che il numero sia in testa alla stringa può essere in mezzo o alla fine