Sorting Numerical Values...

I'll probably slap myself when someone shows me how to do this, but I'm
stuck...

I've got a series of values in a column as follows:

10 Mb
10 Mb
1000 Mb
1000 Mb
114 Mb
128 Mb
128 Mb

Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
because it starts with a "1". How do I tell Excel to sort by the entire
numerical value instead of the first number?

Thanks in advance :-D

Insert a blank column next to your data, and enter a formula like

=LEFT(A1,FIND(" ",A1)-1)

Then, sort by this new column.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Smohrman" <[email protected]> wrote in message
news:[email protected]...
> I'll probably slap myself when someone shows me how to do this,
> but I'm
> stuck...
>
> I've got a series of values in a column as follows:
>
> 10 Mb
> 10 Mb
> 1000 Mb
> 1000 Mb
> 114 Mb
> 128 Mb
> 128 Mb
>
> Obviously 1000 mb is more than 10 mb, but it sorts as second in
> the list
> because it starts with a "1". How do I tell Excel to sort by
> the entire
> numerical value instead of the first number?
>
> Thanks in advance :-D
>
>

If you only use MB you can use numbers instead of text and use a custom
format like

0 "Mb"

then it will sort as

10 Mb
10 Mb
114 Mb
128 Mb
128 Mb
1000 Mb
1000 Mb

otherwise you need to use a help column that will extract the numeric part
and then select both columns and sort by the help column


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Smohrman" <[email protected]> wrote in message
news:[email protected]...
> I'll probably slap myself when someone shows me how to do this, but I'm
> stuck...
>
> I've got a series of values in a column as follows:
>
> 10 Mb
> 10 Mb
> 1000 Mb
> 1000 Mb
> 114 Mb
> 128 Mb
> 128 Mb
>
> Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
> because it starts with a "1". How do I tell Excel to sort by the entire
> numerical value instead of the first number?
>
> Thanks in advance :-D
>
>

Hi Chip,

Sorry, I don't fully understand how to customize the syntax for my situation.
Does the argument "LEFT" designate the colum this formula refers to?
How do I substitute the "A1" value you used in the example with the values
provided in my exampl?
Do I pull a copy then of the whole formula down next to the entire column I
want it to sort?

Thanks for your help.

"Chip Pearson" wrote:

> Insert a blank column next to your data, and enter a formula like
>
> =LEFT(A1,FIND(" ",A1)-1)
>
> Then, sort by this new column.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Smohrman" <[email protected]> wrote in message
> news:[email protected]...
> > I'll probably slap myself when someone shows me how to do this,
> > but I'm
> > stuck...
> >
> > I've got a series of values in a column as follows:
> >
> > 10 Mb
> > 10 Mb
> > 1000 Mb
> > 1000 Mb
> > 114 Mb
> > 128 Mb
> > 128 Mb
> >
> > Obviously 1000 mb is more than 10 mb, but it sorts as second in
> > the list
> > because it starts with a "1". How do I tell Excel to sort by
> > the entire
> > numerical value instead of the first number?
> >
> > Thanks in advance :-D
> >
> >
>
>
>

"Smohrman" wrote:
> I've got a series of values in a column as follows:
> 10 Mb
> 10 Mb
> 1000 Mb
> 1000 Mb
> 114 Mb
> 128 Mb
> 128 Mb
> Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
> because it starts with a "1". How do I tell Excel to sort by the entire
> numerical value instead of the first number?

Assume data in A1 down
Put in B1: =SUBSTITUTE(A1,"Mb","")+0
Copy down

Then sort both cols A & B by col B
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

The LEFT function returns the n left-most characters in a string.
FIND returns the position of a character in a string. So the
formula

=LEFT(A1,FIND(" ",A1)-1)

returns the characters in the cell to the left of the space in
cell A1.

The formula assumes your data starts in cell A1. If it doesn't,
change the A1 (both occurrences) to the first cell of your data
and put the formula in the cell to the right of your data. Then,
select the cells in the new column down as far as your data goes,
and choose Fill Down from the Edit menu.

Finally sort your worksheet data using the new column as the sort
key.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Smohrman" <[email protected]> wrote in message
news:[email protected]...
> Hi Chip,
>
> Sorry, I don't fully understand how to customize the syntax for
> my situation.
> Does the argument "LEFT" designate the colum this formula
> refers to?
> How do I substitute the "A1" value you used in the example with
> the values
> provided in my exampl?
> Do I pull a copy then of the whole formula down next to the
> entire column I
> want it to sort?
>
> Thanks for your help.
>
> "Chip Pearson" wrote:
>
>> Insert a blank column next to your data, and enter a formula
>> like
>>
>> =LEFT(A1,FIND(" ",A1)-1)
>>
>> Then, sort by this new column.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>> "Smohrman" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > I'll probably slap myself when someone shows me how to do
>> > this,
>> > but I'm
>> > stuck...
>> >
>> > I've got a series of values in a column as follows:
>> >
>> > 10 Mb
>> > 10 Mb
>> > 1000 Mb
>> > 1000 Mb
>> > 114 Mb
>> > 128 Mb
>> > 128 Mb
>> >
>> > Obviously 1000 mb is more than 10 mb, but it sorts as second
>> > in
>> > the list
>> > because it starts with a "1". How do I tell Excel to sort
>> > by
>> > the entire
>> > numerical value instead of the first number?
>> >
>> > Thanks in advance :-D
>> >
>> >
>>
>>
>>

Thanks guys- that last formula solved it. I won't be slapping myself
though...it wasn't something I'd have easily figured out.

Appreciate the help!

"Max" wrote:

> "Smohrman" wrote:
> > I've got a series of values in a column as follows:
> > 10 Mb
> > 10 Mb
> > 1000 Mb
> > 1000 Mb
> > 114 Mb
> > 128 Mb
> > 128 Mb
> > Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
> > because it starts with a "1". How do I tell Excel to sort by the entire
> > numerical value instead of the first number?
>
> Assume data in A1 down
> Put in B1: =SUBSTITUTE(A1,"Mb","")+0
> Copy down
>
> Then sort both cols A & B by col B
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>

If all your entries are Mb then try entering the numbers and custom
formatting the cell as #### "Mb" It then should sort as numbers. If the
data is already entered then use

=--LEFT(cell containing data,LEN(cell containing data)-3)
and past special back into the range

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"Smohrman" <[email protected]> wrote in message
news:[email protected]...
> I'll probably slap myself when someone shows me how to do this, but I'm
> stuck...
>
> I've got a series of values in a column as follows:
>
> 10 Mb
> 10 Mb
> 1000 Mb
> 1000 Mb
> 114 Mb
> 128 Mb
> 128 Mb
>
> Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
> because it starts with a "1". How do I tell Excel to sort by the entire
> numerical value instead of the first number?
>
> Thanks in advance :-D
>
>

"Smohrman" wrote:
> Thanks guys- that last formula solved it. I won't be slapping myself
> though...it wasn't something I'd have easily figured out.
> Appreciate the help!

Glad it worked for you!
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hello,

And now introduce Kb and/or Gb, too, please :-)

I tried to make it a habit NEVER to do math calcs on output formats.
One change on the format and you have to change (almost) everything in
your calculations (within Excel).

See Peo's advice.

Regards,
Bernd