software

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 quot;1quot;. 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(quot; quot;,A1)-1)

Then, sort by this new column.--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;Smohrmanquot; gt; wrote in message
...
gt; I'll probably slap myself when someone shows me how to do this,
gt; but I'm
gt; stuck...
gt;
gt; I've got a series of values in a column as follows:
gt;
gt; 10 Mb
gt; 10 Mb
gt; 1000 Mb
gt; 1000 Mb
gt; 114 Mb
gt; 128 Mb
gt; 128 Mb
gt;
gt; Obviously 1000 mb is more than 10 mb, but it sorts as second in
gt; the list
gt; because it starts with a quot;1quot;. How do I tell Excel to sort by
gt; the entire
gt; numerical value instead of the first number?
gt;
gt; Thanks in advance :-D
gt;
gt;
If you only use MB you can use numbers instead of text and use a custom
format like

0 quot;Mbquot;

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

nwexcelsolutions.comquot;Smohrmanquot; gt; wrote in message
...
gt; I'll probably slap myself when someone shows me how to do this, but I'm
gt; stuck...
gt;
gt; I've got a series of values in a column as follows:
gt;
gt; 10 Mb
gt; 10 Mb
gt; 1000 Mb
gt; 1000 Mb
gt; 114 Mb
gt; 128 Mb
gt; 128 Mb
gt;
gt; Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
gt; because it starts with a quot;1quot;. How do I tell Excel to sort by the entire
gt; numerical value instead of the first number?
gt;
gt; Thanks in advance :-D
gt;
gt;
Hi Chip,

Sorry, I don't fully understand how to customize the syntax for my situation.
Does the argument quot;LEFTquot; designate the colum this formula refers to?
How do I substitute the quot;A1quot; 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.

quot;Chip Pearsonquot; wrote:

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

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

Assume data in A1 down
Put in B1: =SUBSTITUTE(A1,quot;Mbquot;,quot;quot;) 0
Copy down

Then sort both cols A amp; B by col B
--
Max
Singapore
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(quot; quot;,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

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

quot;Maxquot; wrote:

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

If all your entries are Mb then try entering the numbers and custom
formatting the cell as #### quot;Mbquot; 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.ukquot;Smohrmanquot; gt; wrote in message
...
gt; I'll probably slap myself when someone shows me how to do this, but I'm
gt; stuck...
gt;
gt; I've got a series of values in a column as follows:
gt;
gt; 10 Mb
gt; 10 Mb
gt; 1000 Mb
gt; 1000 Mb
gt; 114 Mb
gt; 128 Mb
gt; 128 Mb
gt;
gt; Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
gt; because it starts with a quot;1quot;. How do I tell Excel to sort by the entire
gt; numerical value instead of the first number?
gt;
gt; Thanks in advance :-D
gt;
gt;
quot;Smohrmanquot; wrote:
gt; Thanks guys- that last formula solved it. I won't be slapping myself
gt; though...it wasn't something I'd have easily figured out.
gt; Appreciate the help!

Glad it worked for you!
Thanks for the feedback ..
--
Max
Singapore
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