BET: Microsoft Excel Formatting Numbers

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Formatting Numbers > Advanced Techniques

< Previous | Next >

Formatting Characters

	It is possible to format different parts of a text string with the help of some VBA code.
	Lets suppose we want to change the formatting of the following text so the different components are displayed in different colours and formatted differently.
	Please note that this method cannot be used to format dates, only text entries.

The following lines of code loop through all the cells and manually change the formatting of each cell.

1
2
3
4
5
6
7
8
9
10

Range("B2").Select
Do While Len(ActiveCell.Value) > 0
   ActiveCell.Characters(Start:=1, Length:=2).Font.ColorIndex = 9
   ActiveCell.Characters(Start:=1, Length:=2).Font.Bold = True
   ActiveCell.Characters(Start:=4, Length:=2).Font.ColorIndex = 22
   ActiveCell.Characters(Start:=4, Length:=2).Font.Italic = True
   ActiveCell.Characters(Start:=7, Length:=4).Font.ColorIndex = 49
   ActiveCell.Characters(Start:=7, Length:=4).Font.Underline = True
   ActiveCell.Offset(1,0).Select
Loop

Other

	Numerical values by default are left aligned, however you can use the text alignment prefix characters to change this. Apostrophe(') left aligns. Double quotation marks (") right aligns. Caret (^) centre aligns and a Backslash (\) repeats the characters across the cell.
	If you are using text alignment prefix characters to align text then the Caret and Double quotation will only work if you have the following selected (Tools > Options)(Transition tab, "Transition Navigation Keys").
	Every time you use a text alignment prefix character on a numerical value a small flag will appear in the upper left corner, indicating a potential problem. If this is intentional, just choose "Ignore Error" from the smart tag menu.
	You can suppress the display of positive, negative or zero value by creating a custom number format in which the appropriate section is left blank.
	You can format a number as text by prefixing it with an apostrophe (‘) or entering it as formula surrounded by double quotes.
	If you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.
	Numbers in Microsoft Excel can never have more than 15 significant digits, but decimals can be as large as 127.
	Any cells that are formatted as percentages are automatically multiplied by 100 so remember to enter them as decimals.
	Sometimes when numbers are imported or copied into Excel they are sometimes converted to text

	A quick way to convert numbers that are formatted as text to numbers is to multiple them by 1. An alternative is to use the VALUE() function and then copy this down the range
	Replacing all "=" with "+" will have the same effect as re-calculating all the formulas in the selection

	If a cell is displaying a formula as opposed to the value it could be that the cell is formatted as text.
	If you enter a plus sign before a numerical value, then this plus sign is ignored.
	If you enter a negative sign before a numerical value, then this is treated as a negative number.
	If you see the letter "E" or "e" in a numerical value then this is to denote the exponent and represents "10 to the power" (i.e. 1E7 = 10,000,000).
	If you use a forward slash in a numerical value but the entry is not a valid date then this will be interpreted as a fraction (e.g. 5 3/4 = 5.75) (e.g. 1/2 = January 2).
	To display a number format that displays millions without showing the 6 zeros use the custom format "0,,".
	To quickly remove all formats from a range of cells select (Ctrl + Shift + "~").
	To quickly reset the number formats for a selection, press (Ctrl + Shift + "~").
	To create a text entry that consists entirely of numbers, you can precede the entry with a text alignment prefix character, typically an apostrophe. (e.g. '123).
	You can use a number format to change the colour of your cells. (e.g. "[Blue]$#,#0.00_);(£0.00)").
	If you enter a numerical value with a percentage sign then Excel will automatically apply the percentage number format "0.0%". This is equivalent to dividing by 100.
	Numbers that are entered in brackets are treated as negative numbers.
	To make sure that your fractions are never interpreted as dates precede the fraction with a 0 followed by a space. Excel will use the Fraction number format.

If you enter a dollar sign before any numerical value then Excel will automatically apply the currency number format "-$1,234.10".

	A numerical value in a cell will only maintain precision up to a maximum of 15 digits. Excel will convert any digits after the first 15 to zero.
	Any numerical value that is too long for the width of the cell is automatically converted to scientific notation, i.e. exponent.
	It is important to remember that the number that is "displayed" in a cell is not necessarily the same as the "underlying" value that will appear in the formula bar. The number that is displayed depends on the number formatting and the width of the cell.
	You can enter a numerical value as text into a cell by enclosing it in double speech marks (e.g. ="00112233").

Things to Remember

If you are going to format your numerical data, try and avoid using red as this colour is normally associated with negative values.

< Previous | Top | Next >

Leading the way in Microsoft Office Development

Formatting Characters

It is possible to format different parts of a text string with the help of some VBA code.

Lets suppose we want to change the formatting of the following text so the different components are displayed in different colours and formatted differently.

Please note that this method cannot be used to format dates, only text entries.

The following lines of code loop through all the cells and manually change the formatting of each cell.

Other

Numerical values by default are left aligned, however you can use the text alignment prefix characters to change this. Apostrophe(') left aligns. Double quotation marks (") right aligns. Caret (^) centre aligns and a Backslash (\) repeats the characters across the cell.

If you are using text alignment prefix characters to align text then the Caret and Double quotation will only work if you have the following selected (Tools > Options)(Transition tab, "Transition Navigation Keys").

Every time you use a text alignment prefix character on a numerical value a small flag will appear in the upper left corner, indicating a potential problem. If this is intentional, just choose "Ignore Error" from the smart tag menu.

You can suppress the display of positive, negative or zero value by creating a custom number format in which the appropriate section is left blank.

You can format a number as text by prefixing it with an apostrophe (‘) or entering it as formula surrounded by double quotes.

If you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.

Numbers in Microsoft Excel can never have more than 15 significant digits, but decimals can be as large as 127.

Any cells that are formatted as percentages are automatically multiplied by 100 so remember to enter them as decimals.

Sometimes when numbers are imported or copied into Excel they are sometimes converted to text

A quick way to convert numbers that are formatted as text to numbers is to multiple them by 1. An alternative is to use the VALUE() function and then copy this down the range

Replacing all "=" with "+" will have the same effect as re-calculating all the formulas in the selection

If a cell is displaying a formula as opposed to the value it could be that the cell is formatted as text.

If you enter a plus sign before a numerical value, then this plus sign is ignored.

If you enter a negative sign before a numerical value, then this is treated as a negative number.

If you see the letter "E" or "e" in a numerical value then this is to denote the exponent and represents "10 to the power" (i.e. 1E7 = 10,000,000).

If you use a forward slash in a numerical value but the entry is not a valid date then this will be interpreted as a fraction (e.g. 5 3/4 = 5.75) (e.g. 1/2 = January 2).

To display a number format that displays millions without showing the 6 zeros use the custom format "0,,".

To quickly remove all formats from a range of cells select (Ctrl + Shift + "~").

To quickly reset the number formats for a selection, press (Ctrl + Shift + "~").

To create a text entry that consists entirely of numbers, you can precede the entry with a text alignment prefix character, typically an apostrophe. (e.g. '123).

You can use a number format to change the colour of your cells. (e.g. "[Blue]$#,#0.00_);(£0.00)").

If you enter a numerical value with a percentage sign then Excel will automatically apply the percentage number format "0.0%". This is equivalent to dividing by 100.

Numbers that are entered in brackets are treated as negative numbers.

To make sure that your fractions are never interpreted as dates precede the fraction with a 0 followed by a space. Excel will use the Fraction number format.

If you enter a dollar sign before any numerical value then Excel will automatically apply the currency number format "-$1,234.10".

A numerical value in a cell will only maintain precision up to a maximum of 15 digits. Excel will convert any digits after the first 15 to zero.

Any numerical value that is too long for the width of the cell is automatically converted to scientific notation, i.e. exponent.

It is important to remember that the number that is "displayed" in a cell is not necessarily the same as the "underlying" value that will appear in the formula bar. The number that is displayed depends on the number formatting and the width of the cell.

You can enter a numerical value as text into a cell by enclosing it in double speech marks (e.g. ="00112233").

Things to Remember

If you are going to format your numerical data, try and avoid using red as this colour is normally associated with negative values.