[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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