I have data in Excel that comes across as Megabytes and Gigabytes (i.e 500
MB, 3 GB, 200 BM, etc). How can I format this data so Excel recognizes these
as numbers and not text. I would like Excel to be able to add say 894 MB and
128 MB and come up with a total of 1 GB. Is this possible?

Thanks,

I set up a small table in M1:N3 with these values:

kB 1024
MB =1024*1024
GB =1024*1024*1024

and then with the following in A1:A3 :

500 MB
3 GB
200 kB

I put this formula in B1 and copied it down:

=VALUE(LEFT(A1,FIND(" ",A1)-1))*VLOOKUP(RIGHT(A1,LEN(A1)-FIND("
",A1)),M$1:N$3,2,0)

I got the following in B1:B3 :

524288000
3221225472
204800

which is the true number of bytes (note: it doesn't matter if you have
Mb, MB or mB as the VLOOKUP is not case sensitive). So, applying this
to your example (slightly corrected):

896 Mb 939524096
128 MB 134217728

and the sum of column B divided by N3 gives 1.

Hope this helps.

Pete



On Jul 2, 8:06 pm, NetTech <NetT...@discussions.microsoft.com> wrote:
> I have data in Excel that comes across as Megabytes and Gigabytes (i.e 500
> MB, 3 GB, 200 BM, etc). How can I format this data so Excel recognizes these
> as numbers and not text. I would like Excel to be able to add say 894 MB and
> 128 MB and come up with a total of 1 GB. Is this possible?
>
> Thanks,

Thank you so much for your quick response and taking the time to answer my
question. I think you have solved the 1st part of my question, but 2nd part
would be, how can I display the results in a format that is easy to read.

For instance, is there a formula that displays Gb's if my results are
greater than 5 gigabytes, but then displays Mb's if my results are less than
(or equal to) 5 gigabytes?

EXAMPLE 1
5583457484.8 (bytes) - I would like Excel to display 5.2 Gb's

EXAMPLE 2
4718592000 (bytes) - I would like Excel to display 4500 Mb's

"Pete_UK" wrote:

> I set up a small table in M1:N3 with these values:
>
> kB 1024
> MB =1024*1024
> GB =1024*1024*1024
>
> and then with the following in A1:A3 :
>
> 500 MB
> 3 GB
> 200 kB
>
> I put this formula in B1 and copied it down:
>
> =VALUE(LEFT(A1,FIND(" ",A1)-1))*VLOOKUP(RIGHT(A1,LEN(A1)-FIND("
> ",A1)),M$1:N$3,2,0)
>
> I got the following in B1:B3 :
>
> 524288000
> 3221225472
> 204800
>
> which is the true number of bytes (note: it doesn't matter if you have
> Mb, MB or mB as the VLOOKUP is not case sensitive). So, applying this
> to your example (slightly corrected):
>
> 896 Mb 939524096
> 128 MB 134217728
>
> and the sum of column B divided by N3 gives 1.
>
> Hope this helps.
>
> Pete
>
>
>
> On Jul 2, 8:06 pm, NetTech <NetT...@discussions.microsoft.com> wrote:
> > I have data in Excel that comes across as Megabytes and Gigabytes (i.e 500
> > MB, 3 GB, 200 BM, etc). How can I format this data so Excel recognizes these
> > as numbers and not text. I would like Excel to be able to add say 894 MB and
> > 128 MB and come up with a total of 1 GB. Is this possible?
> >
> > Thanks,
>
>
>

Assuming your value is in B17 (my test cell), you can use this formula
to get what you want:

=IF(B17>=5*2^30,TEXT(B17/2^30,"0.0 Gb"),TEXT(B17/2^20,"0 \Mb"))

Just change the references from B17 (3 of them) to suit your data. You
can copy it down if you have a number of values in the column.

Hope this helps.

Pete

On Jul 3, 2:34 pm, NetTech <NetT...@discussions.microsoft.com> wrote:
> Thank you so much for your quick response and taking the time to answer my
> question. I think you have solved the 1st part of my question, but 2nd part
> would be, how can I display the results in a format that is easy to read.
>
> For instance, is there a formula that displays Gb's if my results are
> greater than 5 gigabytes, but then displays Mb's if my results are less than
> (or equal to) 5 gigabytes?
>
> EXAMPLE 1
> 5583457484.8 (bytes) - I would like Excel to display 5.2 Gb's
>
> EXAMPLE 2
> 4718592000 (bytes) - I would like Excel to display 4500 Mb's
>
>
>
> "Pete_UK" wrote:
> > I set up a small table in M1:N3 with these values:
>
> > kB 1024
> > MB =1024*1024
> > GB =1024*1024*1024
>
> > and then with the following in A1:A3 :
>
> > 500 MB
> > 3 GB
> > 200 kB
>
> > I put this formula in B1 and copied it down:
>
> > =VALUE(LEFT(A1,FIND(" ",A1)-1))*VLOOKUP(RIGHT(A1,LEN(A1)-FIND("
> > ",A1)),M$1:N$3,2,0)
>
> > I got the following in B1:B3 :
>
> > 524288000
> > 3221225472
> > 204800
>
> > which is the true number of bytes (note: it doesn't matter if you have
> > Mb, MB or mB as the VLOOKUP is not case sensitive). So, applying this
> > to your example (slightly corrected):
>
> > 896 Mb 939524096
> > 128 MB 134217728
>
> > and the sum of column B divided by N3 gives 1.
>
> > Hope this helps.
>
> > Pete
>
> > On Jul 2, 8:06 pm, NetTech <NetT...@discussions.microsoft.com> wrote:
> > > I have data in Excel that comes across as Megabytes and Gigabytes (i.e 500
> > > MB, 3 GB, 200 BM, etc). How can I format this data so Excel recognizes these
> > > as numbers and not text. I would like Excel to be able to add say 894 MB and
> > > 128 MB and come up with a total of 1 GB. Is this possible?
>
> > > Thanks,- Hide quoted text -
>
> - Show quoted text -