Logical sort order in listbox

Any ideas on how to make the following list of items:

OP1
OP10
OP11
OP2
SCR

sort as:

OP1
OP2
OP10
OP11
SCR

in a listbox?

I want all of the digits in the string to be used in the sort, not digit by digit.

Should I break up the text characters vs digits and then use the Format function:

Format("1", "0#")
Format("2", "0#")
Format("10", "0#")

Or, how about regular expressions? I think I could do this rather easily with Perl regular expressions, but I'm not sure if the VBA equivalent can do this.

I'm thinking others have already tackled this issue and may have a solution.

Thanks.

 

Its always been a problem, e.g. even with filenames in a folder Windows will
sort them that way. I use names like: OP01, OP10. If you don't want to
compare char-by-char, but numerical value, then you will have to break up
the string like you suggested. You can use the Right() function to get at
"numerical" portion.

--
----
Ed
----

with Perl regular expressions, but I'm not sure if the VBA equivalent can do
this.

 

As Ed mentioned, you may have to start from the right and use the IsNumeric test to see where the number stops

 

Thanks guys.