how do i sort?

[Guest]

I have a set of data with multiple columns (4). I need to sort my data by
one of the columns which contains both numbers and alpha-numeric
characters---but the cells are formatted as "Text". When I do a sort,
Excel's default is to sort from left to right and it puts all of my numbers
at the top with the alpha-numeric entries at the bottom. Is there a way that
I can sort my data so it doesn't do this? Example below:

Column to be sorted:
110
115
110LEG
115SAP
120
117LEG
117

When using Excel's Sort function it comes out like this:
110
115
117
120
110LEG
115SAP
117LEG

 

[Guest]

You've shown us how it looks and how you DON'T want it to look. How do you
WANT it to look?

 

[Max]

One guess and a quick play to try ..

Assuming the sample data is in col A, A1 down
and is representative (i.e. 3 numerics before the alphas)

Put in B1: =LEFT(TRIM(A1),3)
Copy B1 down

Now select cols A and B,
do a Data > Sort (Sort by *col B*) > Ascending > OK

You'll get the desired? sort order in col A:

110
110LEG
115
115SAP
117LEG
117
120

 

[Guest]

The desired output in this example would be:
110
110LEG
115
115SAP
117
117LEG
120

 

[Max]

One way to try
(a revision to the formula in my earlier response
to you in the other branch)

Assuming the sample data is in col A, A1 down
and is representative (i.e. 3 numerics before the alphas)

Put in B1:

=IF(A1="","",IF(LEN(A1)=3,A1,LEFT(TRIM(A1),3)+ROW()/10^10))

Copy B1 down

Now select cols A and B,
do a Data > Sort (Sort by *column B*) > Ascending > OK

You'll get the desired sort order in col A:

110
110LEG
115
115SAP
117
117LEG
120

 

[Max]

Put in B1:

=IF(A1="","",IF(LEN(A1)=3,A1,LEFT(TRIM(A1),3)+ROW()/10^10))
Copy B1 down

Just an add-on. With the above formula in col B, instead of doing Data >
Sort, think we could also drive out the same sort order by putting in say,
C1:

=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

and copying C1 down

 

[Guest]

Thanks Max. One thing I forgot to mention was that my data set contains more
than 1 column (about 12 columns) and I am trying to sort the data by the
column with the alpha-numeric data as in my example.

 

[Guest]

Thanks Max! Works perfect---even with multiple columns of data.

 

[Max]

Glad it worked !
Thanks for the feedback ..