Need formula to parse data

[djl0525]

Setup
Sheet1!H5:AA5 contains alphanumeric entries like X3, N2, E1, G4, H2, AL1, B1, etc. The alpha part of the entries is unique and can be one or two characters. The numeric part is always one digit (1-9).

Goal
I need to get the numbers that were entered in random order from Sheet1 to a specific order on Sheet2 (without the alphas).

Example 1
In Sheet2!H5, I need a formula that will look at Sheet1!$H5:$AA5 and if any cell in that range begins with the letter "G", return the number that comes after G, which is 4. If there is no G, return blank.

Example 2
In another cell, the formula will be edited to look for entries that begin with "AL", for example, and return the number after AL, which is 1. If there is no AL in the range, return blank.

Note: Each cell on Sheet2 will look for a different alpha on Sheet1 and return the value after the alpha.

Any assistance will be greatly appreciated!
-- DJ

 

[Sal Paradise]

Glad it's working. I'm sure there's a better way to do it, but I don't know it.