Formula to sort data

[inky]

i would like to create a formula to be placed in column b that sorts data in column a.

i've attached an example worksheet to show how the data should be sorted.

a helper column containing "A1, A2 etc" may be helpful, yet i'm unsure how to sort alpha-numeric results. there are also numeric results (eg 12, 13).

thanks in advance

[VBA Noob]

Maybe

=IF(ISNUMBER(LEFT(A2,2)+0),"z"&LEFT(A2,2),LEFT(A2,2))

VBA Noob

[inky]

thanks VBA Noob.

that's helpful for the helper cell, but what formula can be used to sort the data into order?

[VBA Noob]

Formula don't sort

Add my formula to Col B and then use excel sort

VBA Noob

[Jazzer]

Hi,

this uses two helper columns, but it also sorts the data.

- Asser

[inky]

thanks jazzer. that formula looks very helpful! may i ask why you need the first helper column?

[Jazzer]

You're welcome.

That's just because you wanted to sort so that the numbers comes after letters. Normally if you sort in Excel, the numbers comes first. That's why I add an extra ! before any letters, because ! is "smaller" than any number.

- Asser

[inky]

thanks jazzer. you're a star!