Excel cell format help

[alan147]

Good afternoon

I have some disc space data that is a mixture of GB and MB for example 5.9 GB and 250 MB, is there a way to:

1. convert all of the data to GB ie the 250 MB would become 0.25 GB

2. Remove the text, MB and GB

Thanks

Alan

 

[HarleyQuinn]

Might not be the best way to do this but it does work:

=LEFT(A1,LEN(A1)-2)/1024

Hope it helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[bluedragon2]

Use a combination of FIND and IF formulas to adjust the math:

For example:

=IF(RIGHT(A1,2)="Mb",VALUE(LEFT(A1,FIND(" ",A1)-1))/1000,VALUE(LEFT(A1,FIND(" ",A1)-1)))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[HarleyQuinn]

Oh, I forgot to allow for MB amounts, wrap it in an IF statement to check that.

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SkipVought]

FYI, this is not a FORMAT issue. This is a TEXT manipulation & CONVERSION issue.

Cell Format only refers to NUMBERS. You have TEXT, like 250 MB.

Cell FORMAT, does not change the underlying value.

This may seem like a distinction without a difference, but FORMAT in Excel means something very speecific.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[bluedragon2]

I just assist, Skip provides help with knowledge...

If I could just tap into that knowledge and download it into my brain....

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[yogia]

Hi Alan:

Or you can use ...

=REPLACE(A1,FIND("B",A1)-1,2,"")/(IF(RIGHT(A1,2)="MB",1000,1))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[HarleyQuinn]

Just a thought, but dividing by 1000 isn't a true representation of MB to GB...

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[alan147]

Thanks for all the help, I used Yogi's code. It works perfectly.

Alan

 

[alan147]

I have a follow up question.

My spreadsheet looks like this

Date Volume / Volume /var %Used / %Used /opt
9/10/2007 2.0 GB 3.7 GB 69.97% 63.79%
10/10/2007 2.0 GB 3.7 GB 69.97% 63.79%
11/10/2007 2.0 GB 3.7 GB 69.97% 63.79%
12/10/2007
13/10/2007

The percent used columns are then used to create a graph.

The formula =(REPLACE(J99,FIND("B",J99)-1,2,"")/(IF(RIGHT(J99,2)="MB",1000,1))/RefData!$B$12)
caluclates the % used. Obviously future dates in the volume columns have no data in, what I would like to be able to do is populate all of the % used cells with the formula so that as additional data is added in to the volume columns the calculation is performed and the graph updated. Currently filling the % used columns will return #VALUE when there is no data to calculate. I there a way to make the calculation ignore empty cells? I have tried the ISNUMBER and ISEMPTY options to no avail.

Thanks

Alan