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

Sorting Numerical Values...
 

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" wrote in message
...
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

Sorting Numerical Values...
 

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" wrote in message
...
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

Sorting Numerical Values...
 

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" wrote in message
...
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

Sorting Numerical Values...
 

"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
---

Sorting Numerical Values...
 

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" wrote in message
...
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" wrote in
message
...
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

Sorting Numerical Values...
 

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
---

Sorting Numerical Values...
 

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


with @tiscali.co.uk


"Smohrman" wrote in message
...
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

Sorting Numerical Values...
 

"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
---

Sorting Numerical Values...
 

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