Rick Rothstein (MVP - VB) wrote:
While the original data didn't include any without trailing numbers
(e.g., "Solent") or without a space between the trailing number and the
text that comes before it (e.g., "Solent36"), you can generalize your
function to handle the given data plus these variations by changing the
subtraction of 2 to a subtraction of 1 and using the TRIM function...

=UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9} ,A2&"0123456789"))-1)))

Rick

This works beautifully.

Thanks to all.