ORDER BY <an expression to remove leading numbers>
The key issue might be what format variations in the column?
suppose one of simplest case that the format was fixed with "nnnnn_ccc" where n is a number and c is an alphabet.
Then, <an expression to remove leading numbers> might be
SUBSTR(a_column , -3) /* result: 'ccc' */
SUBSTR(a_column , -4) /* result: '_ccc' */