Sorting Numerical Values...

[Guest]

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

 

[Chip Pearson]

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

 

[Peo Sjoblom]

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

 

[Guest]

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.

 

[Guest]

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

---

 

[Chip Pearson]

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

 

[Guest]

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!

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

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

---

 

[bplumhoff]

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