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

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

"MXC" wrote:

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

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"MXC" wrote in message
...
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

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

"Gary Brown" wrote:

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

"MXC" wrote:

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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"MXC" wrote in message
...
The desired output in this example would be:
110
110LEG
115
115SAP
117
117LEG
120

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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.

"Max" wrote:

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

"Max" wrote:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"MXC" wrote in message
...
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

Glad it worked !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"MXC" wrote in message
...
Thanks Max! Works perfect---even with multiple columns of data.