• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

Viewing numbers with data in the column as numbers

I have a spreadsheet where I have imported numbers into a .csv file from a script I wrote. The problem is the numbered value has data in the column also and I would like to be able to sort by numbers "smallest to largest" and not A-Z z-a. By the way I am using Office 2010/excel 2010.


EX

names       sizes
john            50MB
Mary            3mb

Is there anyway this can be accomplished, thanks
0
techdrive
Asked:
techdrive
  • 4
  • 2
  • 2
1 Solution
 
RyanProject Engineer, ElectricalCommented:
There might be a better way, but I would strip the text from Col2, then it will be numeric and you can sort.

Assuming the format is always #MB then
C3 = val(left(B2,length(b2-2)))
0
 
Arno KosterCommented:
so you mean you would like to sort on the number of megabytes for example ?

low to high :
mary = 3
john = 50
bert = 100
0
 
techdriveAuthor Commented:
The problem with that is we need those numbers to validate the sizes of the mailboxes. For example MB -s megabyte, GB gigabyte and so forth.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Arno KosterCommented:
add a column 'value' (the C-column) next to 'size' (the B-column)
use this formula in cell C2:
=VALUE(LEFT(B2, LEN(B2)-2))

Open in new window


this takes out the last 2 characters of the size and interprets it as a value instead of a text.
you can then sort on the value column.
0
 
techdriveAuthor Commented:
Yes Akoster but in this scenario I am also dealing with GB, MB and KB.
0
 
Arno KosterCommented:
I see, in that case the formula would be a little bit more complex:
"=if(lower(right(b2,2))=""gb"", 1e9*value(left(b2,len(b2)-2)), if(lower(right(b2,2))=""mb"", 1e6* value(left(b2,len(b2)-2)),if(lower(right(b2,2))=""kb"", 1e3 * value(left(b2,len(b2)-2)),value(left(b2,len(b2)-1)))))"

Open in new window

0
 
Arno KosterCommented:
it basically checks if it is a "GB" value. if so, multiply with 1e9 (1.000.000.000) to get the size in bytes.
if not in GB and it is a MB value, multiply with 1e6 (1.000.000) to get the size in bytes.
if not in GB and not in MB but in KB value, multiply with 1000 to get the size in bytes.
if not in GB and not in MB and not in KB, than the size is already in bytes. strip the last character (the 'b' !) to het the size in bytes.
0
 
RyanProject Engineer, ElectricalCommented:
If you have a lot of values, a similar approach to askoster is to use a VLookup to get a factor.

val(left(B2,length(b2-2))) * vlookup(right(b2,2),"H:I",2,false)

Where H has your GB,MB,kb, and I has a factor to multiply by (1E9,1E6,1E3)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now