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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
[email protected]XX
"V-ger" <[email protected]> wrote in message news:[email protected]...
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))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.
[QUOTE=V-ger]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!
Last edited by Bryan Hessey; 11-14-2005 at 07:45 PM.
Ahhh - sorry, didn't read the post correctly - bear with me.
Regards
Ken..................
"V-ger" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
news:[email protected]...
> Ahhh - sorry, didn't read the post correctly - bear with me.
>
> Regards
> Ken..................
>
> "V-ger" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Ahhh - sorry, didn't read the post correctly - bear with me.
>>
>> Regards
>> Ken..................
>>
>> "V-ger" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>> > 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" <[email protected]>
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,(FIND("-",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.
Originally Posted by V-ger
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
> [email protected]XX
>
>
>
> "V-ger" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> Ahhh - sorry, didn't read the post correctly - bear with me.
> >>
> >> Regards
> >> Ken..................
> >>
> >> "V-ger" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>>> news:[email protected]...
> >>>> > 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" <[email protected]>
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks