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

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 152

# 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
• 4
• 2
• 2
1 Solution

Project 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

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

Author 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

Commented:
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))
``````

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

Author Commented:
Yes Akoster but in this scenario I am also dealing with GB, MB and KB.
0

Commented:
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)))))"
``````
0

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

Project 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

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