How do I format cells to show GB, MB, KB

[Dickison]

How do I format cells to show a number entered as 18,177,538,430 to display as 18.2GB

 

[Tom Urtis]

Given your example, this custom number format would do that:
#,##0.0,,,"GB"

Select the cell(s) of interest, then click Format > Cells > Number tab, select Custom in the Category pane, and enter said custom format in the narrow Type field to the right. Click OK to exit the Format Cells dialog and you should have what you want, at least where your example is concerned.

 

[Dickison]

Thanks. That worked great for GB. Is there a way to get it to put in GB, MB, KB as the size of the number changes?

 

[Tom Urtis]

If you want the format with those text suffixes to change depending on the ranges of the numbers, you will need VBA if it is such that any size number can be present in any given cell. If some cells will always have a certain size number, then you can apply native custom formatting such as I posted to each respective range depending on the expected number range you designed for that cell range.

Instead of providing your information post by post in bits, please just state all the relevant details of how your sheet is designed so we can know the entire picture without guessing:

(1)
How do the numbers get there in the first place...are they manually entered or are they returned by formulas in those cells?

(2)
Will one range of cells always be expecting a certain size number, or can any size number possibly be present in any cell? Example, is column A only for GB, column B only for MB, and column C only for KB. Or can any cell in maybe column A be subject to a certain sized number and hence you want that respective suffix to apply to its formatting depending on number size.

(3)
What is the range of numbers in each case? Example, for GB, what is the lowest size (minimum) number and what is the highest size (maximum) number for which GB would apply, and also then what are the mins and max's for MB and KB suffixes.

 

[Yogi Anand]

Thanks. That worked great for GB. Is there a way to get it to put in GB, MB, KB as the size of the number changes?

Hi Dickinson:

Let us have a look at ...

Book1
	A	B	C	D	E
1
2		18	18000	18000000
3		18KB	18000MB	18000000GB
4		CustomNumberFormat[<1000]#"KB";[>1000000]#"GB";#"MB"
5
Sheet3

Would this do?

 

[Dickison]

Sorry about not posting all the details. What I'm doing is keeping track of disk space on all our servers. I have a utility, Dameware, that I export the disk sizes and free space for all the servers. I then import the .csv file created from Dameware, which shows disk info in bytes. I then use the info imported in a weekly report that lists the servers, disk size, free space, and percent free.

This is the format of the import file:

ServerName, Drive, Format, Size Free, Used, PercentFree, PercentUsed
Server1, C: NTFS, "36,314,603,050", "26,413,903,780", "9,901,709,870", 72.73, 27.27
Server1, D: NTFS, "181,819,397,078", "173,242,532,804", "8,567,761,834", 95.29, 4.71

Let me know if you need additional info.

 

[Yogi Anand]

Sorry about not posting all the details. What I'm doing is keeping track of disk space on all our servers. I have a utility, Dameware, that I export the disk sizes and free space for all the servers. I then import the .csv file created from Dameware, which shows disk info in bytes. I then use the info imported in a weekly report that lists the servers, disk size, free space, and percent free.

This is the format of the import file:

ServerName, Drive, Format, Size Free, Used, PercentFree, PercentUsed
Server1, C: NTFS, "36,314,603,050", "26,413,903,780", "9,901,709,870", 72.73, 27.27
Server1, D: NTFS, "181,819,397,078", "173,242,532,804", "8,567,761,834", 95.29, 4.71

Let me know if you need additional info.

Hi Dickison:

Let us have a look at ...

Book1
	A	B	C	D	E	F	G	H
1
2	ServerName,	Drive,	Format,	Size	Free,	Used,	PercentFree,	PercentUsed
3	Server1	C:	NTFS	36314603050	26413903780	990170987	72.73	27.27
4	Server1	D:	NTFS	36.315GB	26.414GB	990.171MB	95.29	4.71
5				CustomNumberFormat[>1000000000]0.000,,,"GB";[>1000000]0.000,,"MB";0.000,"KB"
6
Sheet3 (2)

I hope this helps!

 

[Dickison]

I copied and pasted this

[>1000000000]0.000,,,"GB";[>1000000]0.000,,"MB";0.000,"KB"

It gives an error saying that this format cannot be used. I am using Excel 2003.

Looking in the help file it shows '>' instead of '&gt'. I replaced those and the &nbsp with a space but it's not quite working yet. I'm trying to get TB in also. This is what I have:

[>1000000000000]0.000,,," TB";[>1000000000]0.000,,," GB";[>1000000]0.000,,," MB"

What's wrong with it?

 

[Yogi Anand]

I copied and pasted this

[>1000000000]0.000,,,"GB";[>1000000]0.000,,"MB";0.000,"KB"

It gives an error saying that this format cannot be used. I am using Excel 2003.

Looking in the help file it shows '>' instead of '&gt'. I replaced those and the &nbsp with a space but it's not quite working yet. I'm trying to get TB in also. This is what I have:

[>1000000000000]0.000,,," TB";[>1000000000]0.000,,," GB";[>1000000]0.000,,," MB"

What's wrong with it?

Hi Dickison:

Let us have a look at ...

Book1
	A	B	C	D	E	F	G	H
1
2	ServerName,	Drive,	Format,	Size	Free,	Used,	PercentFree,	PercentUsed
3	Server1	C:	NTFS	1000000000000	26413903780	990170987	72.73	27.27
4	Server1	D:	NTFS	1.000TB	26.414GB	990.171MB	95.29	4.71
5				CustomNumberFormat[>999999999999]0.000,,,,"TB";[>999999999]0.000,,,"GB";0.000,,"MB"
6
Sheet3 (3)

CustomNumberFormat for cells D4,E4, and F4 is ...

[>999999999999]0.000,,,,"TB";[>999999999]0.000,,,"GB";0.000,,"MB"

Let me know how it works out for you now!

 

[Dickison]

That works great. Thanks to all of you.