How can I sort by a row of numbers with mixed formats? I have numbers such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no matter how
I format the cells, they sort by first number, then second, then the numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find a way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22, etc.)
Please...any help you can give me. Thank you.

Convert everything to text. Assuming your data is in Col A, then in Col B
use

=""&A2 and copy down. Then copy and paste special as values and delete Col
A. Now sort on your data as you wish.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"V-ger" wrote in message
...
How can I sort by a row of numbers with mixed formats? I have numbers
such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no matter
how
I format the cells, they sort by first number, then second, then the
numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find a
way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22,
etc.)
Please...any help you can give me. Thank you.

Thanks. I'll give it a whirl. (Sorry about duplicate posts...the first one
took so long I thought I had lost it.)

"Ken Wright" wrote:

Convert everything to text. Assuming your data is in Col A, then in Col B
use

=""&A2 and copy down. Then copy and paste special as values and delete Col
A. Now sort on your data as you wish.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"V-ger" wrote in message
...
How can I sort by a row of numbers with mixed formats? I have numbers
such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no matter
how
I format the cells, they sort by first number, then second, then the
numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find a
way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22,
etc.)
Please...any help you can give me. Thank you.

V-ger,

I think you will find that even if you convert all the data to text
that 22 will sort ahead of 3.

My Excel add-in Special Sort can sort on the numbers in a text entry.
(there are 4 different ways to get a true numeric sort order)
It has over 20 different sort methods not readily available in Excel.
They include sorting by...
color, prefix, middle, suffix, random, reverse,
no articles, dates, decimal, length and others.
It comes with a Word.doc install/use file.

It is - free - just email me and ask for it.
Remove XXX from my email address.

Jim Cone
San Francisco, USA
XX



"V-ger" wrote in message ...
How can I sort by a row of numbers with mixed formats? I have numbers such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no matter how
I format the cells, they sort by first number, then second, then the numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find a way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22, etc.)
Please...any help you can give me. Thank you.

Still not sorting correctly. What am I doing wrong? It's better, but the 3
still comes after the 22-A. Two issues...first, does the cell format need to
be "General" or "Number" or what? Second, when I type the formula you wrote
below it didn't work, but when I copied it from your post and pasted it in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!

"Ken Wright" wrote:

Convert everything to text. Assuming your data is in Col A, then in Col B
use

=""&A2 and copy down. Then copy and paste special as values and delete Col
A. Now sort on your data as you wish.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"V-ger" wrote in message
...
How can I sort by a row of numbers with mixed formats? I have numbers
such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no matter
how
I format the cells, they sort by first number, then second, then the
numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find a
way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22,
etc.)
Please...any help you can give me. Thank you.

Try

=IF(ISERROR(FIND("-",A1)),TEXT(A1,"000000"),TEXT(LEFT(A1,(FIND("-",A1)-1)),"000000")&MID(A1,(FIND("-",A1)),99))

where "000000" needs to be long enough to cover your largest number,
and all alpha addons need to be after a '-' character.

V-ger Wrote:
Still not sorting correctly. What am I doing wrong? It's better, but
the 3
still comes after the 22-A. Two issues...first, does the cell format
need to
be "General" or "Number" or what? Second, when I type the formula you
wrote
below it didn't work, but when I copied it from your post and pasted it
in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=485021

Ahhh - sorry, didn't read the post correctly - bear with me.

Regards
Ken..................

"V-ger" wrote in message
...
Still not sorting correctly. What am I doing wrong? It's better, but the
3
still comes after the 22-A. Two issues...first, does the cell format need
to
be "General" or "Number" or what? Second, when I type the formula you
wrote
below it didn't work, but when I copied it from your post and pasted it
in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!

"Ken Wright" wrote:

Convert everything to text. Assuming your data is in Col A, then in Col
B
use

=""&A2 and copy down. Then copy and paste special as values and delete
Col
A. Now sort on your data as you wish.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"V-ger" wrote in message
...
How can I sort by a row of numbers with mixed formats? I have numbers
such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no
matter
how
I format the cells, they sort by first number, then second, then the
numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find
a
way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22,
etc.)
Please...any help you can give me. Thank you.

OK, other than perhaps using Jims addin, the only way I get there without
extra columns is to make all your digits double digit, using

=TEXT(A2,"00")

and copy down. Won't affect the text entries but will convert numerics to
double digit text. Format doesn't matter.

This should sort the way you want it.

Yes you were correct on last note:-

= " " & A 2 but without the spaces

Regards
Ken....................


"Ken Wright" wrote in message
...
Ahhh - sorry, didn't read the post correctly - bear with me.

Regards
Ken..................

"V-ger" wrote in message
...
Still not sorting correctly. What am I doing wrong? It's better, but
the 3
still comes after the 22-A. Two issues...first, does the cell format
need to
be "General" or "Number" or what? Second, when I type the formula you
wrote
below it didn't work, but when I copied it from your post and pasted it
in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!

"Ken Wright" wrote:

Convert everything to text. Assuming your data is in Col A, then in Col
B
use

=""&A2 and copy down. Then copy and paste special as values and delete
Col
A. Now sort on your data as you wish.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"V-ger" wrote in message
...
How can I sort by a row of numbers with mixed formats? I have numbers
such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no
matter
how
I format the cells, they sort by first number, then second, then the
numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to
find a
way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22,
etc.)
Please...any help you can give me. Thank you.

Note though, that this assumes your data is no more than double digit as per
your example data. Any more than that and you need a more comprehensive
formula.

Regards
Ken................

"Ken Wright" wrote in message
...
OK, other than perhaps using Jims addin, the only way I get there without
extra columns is to make all your digits double digit, using

=TEXT(A2,"00")

and copy down. Won't affect the text entries but will convert numerics to
double digit text. Format doesn't matter.

This should sort the way you want it.

Yes you were correct on last note:-

= " " & A 2 but without the spaces

Regards
Ken....................


"Ken Wright" wrote in message
...
Ahhh - sorry, didn't read the post correctly - bear with me.

Regards
Ken..................

"V-ger" wrote in message
...
Still not sorting correctly. What am I doing wrong? It's better, but
the 3
still comes after the 22-A. Two issues...first, does the cell format
need to
be "General" or "Number" or what? Second, when I type the formula you
wrote
below it didn't work, but when I copied it from your post and pasted it
in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!

"Ken Wright" wrote:

Convert everything to text. Assuming your data is in Col A, then in
Col B
use

=""&A2 and copy down. Then copy and paste special as values and delete
Col
A. Now sort on your data as you wish.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"V-ger" wrote in message
...
How can I sort by a row of numbers with mixed formats? I have
numbers
such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no
matter
how
I format the cells, they sort by first number, then second, then the
numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to
find a
way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22,
etc.)
Please...any help you can give me. Thank you.

I'll wait. Thanks! As soon as I get this I can go home. I really
appreciate your help. I did install the add-in that was suggested by Jim, as
well, and am just starting to look at that now...but need to understand the
Excel logic of sorting. So thanks!

"Ken Wright" wrote:

Ahhh - sorry, didn't read the post correctly - bear with me.

Regards
Ken..................

"V-ger" wrote in message
...
Still not sorting correctly. What am I doing wrong? It's better, but the
3
still comes after the 22-A. Two issues...first, does the cell format need
to
be "General" or "Number" or what? Second, when I type the formula you
wrote
below it didn't work, but when I copied it from your post and pasted it
in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!

"Ken Wright" wrote:

Convert everything to text. Assuming your data is in Col A, then in Col
B
use

=""&A2 and copy down. Then copy and paste special as values and delete
Col
A. Now sort on your data as you wish.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"V-ger" wrote in message
...
How can I sort by a row of numbers with mixed formats? I have numbers
such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no
matter
how
I format the cells, they sort by first number, then second, then the
numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find
a
way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22,
etc.)
Please...any help you can give me. Thank you.

On Mon, 14 Nov 2005 14:42:04 -0800, "V-ger"
wrote:

How can I sort by a row of numbers with mixed formats? I have numbers such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no matter how
I format the cells, they sort by first number, then second, then the numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find a way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22, etc.)
Please...any help you can give me. Thank you.

You'll need to set up two helper columns -- one for the numeric portion, and
one for the text portion.

Then sort first on the numbers, then on the text.

I assume that the format is either:

n
or
n-a

as you wrote above.

Assume you have a column of numbers in A1:An

B1: =IF(ISERROR(FIND("-",A1)),A1,LEFT(A1,FIND("-",A1)-1))
C1: =IF(ISERROR(FIND("-",A1)),"",MID(A1,FIND("-",A1)+1,256))

Select B1:C1 and copy/drag down to Bn:Cn

Select A1:Cn
Data/Sort
Sort by Column B Ascending
then by Column C Ascending

Finally, delete or hide columns B&C


--ron

Thanks. Do I copy and paste special, as instructed in Ken's previous email?
Also, do I choose A) Sort anything that looks like a number as a number, or
B) Sort numbers and numbers stored as text seperately? I'm a real novice.
Thanks for your help.

"Bryan Hessey" wrote:


Try

=IF(ISERROR(FIND("-",A1)),TEXT(A1,"000000"),TEXT(LEFT(A1,(FIND("-",A1)-1)),"000000")&MID(A1,(FIND("-",A1)),99))

where "000000" needs to be long enough to cover your largest number,
and all alpha addons need to be after a '-' character.

V-ger Wrote:
Still not sorting correctly. What am I doing wrong? It's better, but
the 3
still comes after the 22-A. Two issues...first, does the cell format
need to
be "General" or "Number" or what? Second, when I type the formula you
wrote
below it didn't work, but when I copied it from your post and pasted it
in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=485021

Apologies, use the formula with the quotes to avoid the 'number'
things.

=IF(ISERROR(FIND("-",A1)),"'"&TEXT(A1,"000000"),"'"&TEXT(LEFT(A1,(FIN D("-",A1)-1)),"000000")&MID(A1,(FIND("-",A1)),99))

and no need to paste, it's all calculated across the row so will not
change by sorting.

V-ger Wrote:
Thanks. Do I copy and paste special, as instructed in Ken's previous
email?
Also, do I choose A) Sort anything that looks like a number as a
number, or
B) Sort numbers and numbers stored as text seperately? I'm a real
novice.
Thanks for your help.

"Bryan Hessey" wrote:


Try


=IF(ISERROR(FIND("-",A1)),TEXT(A1,"000000"),TEXT(LEFT(A1,(FIND("-",A1)-1)),"000000")&MID(A1,(FIND("-",A1)),99))

where "000000" needs to be long enough to cover your largest number,
and all alpha addons need to be after a '-' character.

V-ger Wrote:
Still not sorting correctly. What am I doing wrong? It's better,
but
the 3
still comes after the 22-A. Two issues...first, does the cell
format
need to
be "General" or "Number" or what? Second, when I type the formula
you
wrote
below it didn't work, but when I copied it from your post and
pasted it
in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=485021




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=485021

Hi,

Create a helper column with the following formula (for example, if your data
are in column A starting at A2, use the formula in B2, and fill-in the
formula down the column)

=(IF(ISNUMBER(A2*1),A2*1,LEFT(A2,LEN(A2)-2)))*100+IF(ISNUMBER(A2*1),0,CODE(RIGHT(UPPER(A2), 1))-64)

Now select the entire data and sort by column B.

This formula will not differentiate betwen 21-A and 21-a, and secondly, it
assumes that you won't have data such as 21-AB or 21A.

Regards,
B. R. Ramachandran




"V-ger" wrote:

How can I sort by a row of numbers with mixed formats? I have numbers such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no matter how
I format the cells, they sort by first number, then second, then the numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find a way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22, etc.)
Please...any help you can give me. Thank you.

OH, MY GOSH! Your add-in works like a dream. Thank you everyone for your
help. This add-in is great for novice users like me. I will someday be able
to understand formulas a bit better (soon, I hope) but for now, this add-in
is a great short-cut and works fast. THANK YOU FOR ALL OF YOUR HELP. V-ger

"Jim Cone" wrote:

V-ger,

I think you will find that even if you convert all the data to text
that 22 will sort ahead of 3.

My Excel add-in Special Sort can sort on the numbers in a text entry.
(there are 4 different ways to get a true numeric sort order)
It has over 20 different sort methods not readily available in Excel.
They include sorting by...
color, prefix, middle, suffix, random, reverse,
no articles, dates, decimal, length and others.
It comes with a Word.doc install/use file.

It is - free - just email me and ask for it.
Remove XXX from my email address.

Jim Cone
San Francisco, USA
XX



"V-ger" wrote in message ...
How can I sort by a row of numbers with mixed formats? I have numbers such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no matter how
I format the cells, they sort by first number, then second, then the numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find a way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22, etc.)
Please...any help you can give me. Thank you.

Bryan, Thanks for your help. I decided to use the add-in, it's just two
clicks. But please answer a question - since I will use your formula to learn
about formulas...when I tried it, I substituted A1 for the actual first bit
of information that I wanted to sort, which was A5. Was that correct? Thank
you.

"Bryan Hessey" wrote:


Try

=IF(ISERROR(FIND("-",A1)),TEXT(A1,"000000"),TEXT(LEFT(A1,(FIND("-",A1)-1)),"000000")&MID(A1,(FIND("-",A1)),99))

where "000000" needs to be long enough to cover your largest number,
and all alpha addons need to be after a '-' character.

V-ger Wrote:
Still not sorting correctly. What am I doing wrong? It's better, but
the 3
still comes after the 22-A. Two issues...first, does the cell format
need to
be "General" or "Number" or what? Second, when I type the formula you
wrote
below it didn't work, but when I copied it from your post and pasted it
in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=485021

Ken, Thank you so much. The numbers do vary in number of digits. I really
appreciate all your help with this problem, you were terrific. I installed
and used the add-in sent by Jim and it is working well so far. It did solve
my immediate issue. Keep up the good work. I have so much to learn about
Excel, and I am encouraged by your patient help. - V-ger

"Ken Wright" wrote:

Note though, that this assumes your data is no more than double digit as per
your example data. Any more than that and you need a more comprehensive
formula.

Regards
Ken................

"Ken Wright" wrote in message
...
OK, other than perhaps using Jims addin, the only way I get there without
extra columns is to make all your digits double digit, using

=TEXT(A2,"00")

and copy down. Won't affect the text entries but will convert numerics to
double digit text. Format doesn't matter.

This should sort the way you want it.

Yes you were correct on last note:-

= " " & A 2 but without the spaces

Regards
Ken....................


"Ken Wright" wrote in message
...
Ahhh - sorry, didn't read the post correctly - bear with me.

Regards
Ken..................

"V-ger" wrote in message
...
Still not sorting correctly. What am I doing wrong? It's better, but
the 3
still comes after the 22-A. Two issues...first, does the cell format
need to
be "General" or "Number" or what? Second, when I type the formula you
wrote
below it didn't work, but when I copied it from your post and pasted it
in,
it worked as a formula - is it equals, quote, quote, ampersand,
cell-address-of-the-cell-to-the-left? Thank you so much!

"Ken Wright" wrote:

Convert everything to text. Assuming your data is in Col A, then in
Col B
use

=""&A2 and copy down. Then copy and paste special as values and delete
Col
A. Now sort on your data as you wish.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"V-ger" wrote in message
...
How can I sort by a row of numbers with mixed formats? I have
numbers
such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no
matter
how
I format the cells, they sort by first number, then second, then the
numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to
find a
way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22,
etc.)
Please...any help you can give me. Thank you.

Ron, Thanks, but I decided to go with the add-in. It was so much faster.
Have a good evening. V-ger

"Ron Rosenfeld" wrote:

On Mon, 14 Nov 2005 14:42:04 -0800, "V-ger"
wrote:

How can I sort by a row of numbers with mixed formats? I have numbers such
as 1, 2, 3 and 10, 22, 27 and 11-A, 20-D, 34-D etc. Currently, no matter how
I format the cells, they sort by first number, then second, then the numbers
with letters (1, 10, 2, 22, 27, 11-A, 20-D etc.). I can't seem to find a way
to get the column to sort by the actual value (1, 2, 3, 10, 11-A, 22, etc.)
Please...any help you can give me. Thank you.

You'll need to set up two helper columns -- one for the numeric portion, and
one for the text portion.

Then sort first on the numbers, then on the text.

I assume that the format is either:

n
or
n-a

as you wrote above.

Assume you have a column of numbers in A1:An

B1: =IF(ISERROR(FIND("-",A1)),A1,LEFT(A1,FIND("-",A1)-1))
C1: =IF(ISERROR(FIND("-",A1)),"",MID(A1,FIND("-",A1)+1,256))

Select B1:C1 and copy/drag down to Bn:Cn

Select A1:Cn
Data/Sort
Sort by Column B Ascending
then by Column C Ascending

Finally, delete or hide columns B&C


--ron