VBA: how can I extract numbers from text for a basic pigeonhole sort?

VBA: how can I extract numbers from text for a basic pigeonhole sort?

so im trying to code a kind of pigeon hole sorting function thing. I will
be calling it to arrange an array containing the summary of values from a
column. It could contain AHU1, AHU3, AHU2 or EF2, EF1, EF3 or just 3, 1,
2. There will always be a number contained as its is required by the
input. My first attempt is below, this seemes inefficient. For both, Adim
is the size of the number of vlaues in the input array (ArrayToSort) and
TempAry() is a temporary array that will provide the output of the sort
function. i and j are just integers.
For i = ADim To 0
For j = 1 To ADim + 1
If AryToSort(i) = "*" & j Then ''''this doesnt work, im only 2
weeks old to vba so still learning but thought it might work
TempAry(j - 1) = AryToSort(i).Value
End If
Next
Next
Second attempt is below but is just psuedo atm still have the problem that
i cant get a number from the reference in the array. Just 1 for loop this
time. There will never be a case where there is a discontinuos series.
For i = ADim To 0
j = number extracted from AryToSort(i)
TempAry(j - 1) = AryToSort(i).Value
End If
Next
I have looked into using Right(), Mid() and Left() but dont think it will
work, also using split functions. I could do this with split if i allow
another input into the sort function (which currently just pulls in the
array) which would have the length of the text string in the array so i
can use a split function but would prefer to avoid this.
Any ideas, pointers, or complete rethinks?