Excel, Custom Formatting Semicolon Character

Sep 04, 2015 by azurous in Custom Formatting

The semicolon character is a special character used in custom formatting. It basically acts as an IF statement, it checks for the following conditions:

Positive numbers
Negative numbers
Zeros
Text Values

You can have upto 3 semicolons in a custom formatting.

Note: The rules mentioned in this article will be overridden if the semicolon is used for checking custom conditions as explained in the article below:

Excel Custom Formatting Conditions

Jump To:

No Semicolon
One Semicolon Without @ Sign, <POSITIVE and ZERO>;<NEGATIVE>
One Semicolon With @ Sign <NUMERIC>;<TEXT>
Two Semicolons Without @ Sign, <POSITIVE>;<NEGATIVE>;<ZERO>
Two Semicolons With @ Sign <POSITIVE and ZERO>;<NEGATIVE>;<TEXT>
Three Semicolons, <POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

–

Contents

No Semicolons:

Without using a semicolon the formatting applied to the cell will be applied to any input regardless of its type and sign:

Positive numbers
Negative numbers
Zero
Text

The same formatting will be applied to all of them

–

One Semicolon Without @ Sign, <POSITIVE and ZERO>;<NEGATIVE>:

If one semicolon is used, then the custom formatting will be split in 2 sections. The section before the semicolon and the section after the semicolon. If an @ sign is NOT used in the second section the following will apply:

Section 1: The custom formatting in this section will only apply to positive numbers and zeros.
Section 2: The custom formatting in this section will apply to negative numbers.

Result: <POSITIVE and ZERO>;<NEGATIVE>

For more information about the @ sign please see the article below:

Excel Custom Formatting, @ Character

I will explain this topic using examples.

Example 1:

The values below are printed in column A:

We will be applying the following custom formatting to the cells in column A:

"Pos" 0; "Neg" 0

As you can see the custom formatting has 2 sections, the section before the semicolon:

"Pos" 0

and the section after the semicolon:

"Neg" 0

The section before the semicolon applies to positive numbers and zero, while the section after the semicolon applies to negative numbers. If the number in column A is positive or zero then it will be printed with a “Pos” text before it using the “0” numeric format. If the number is negative then it will be printed with a “Neg” text before it with the “0” numeric format.

Result:

Example 2:

Also this is another example with the same date in column A. The custom formatting below is used:

[Blue]0;[Red]-0

If the number is positive or zero it is printed in blue using the “0” numeric format. If the number is negative then it is printed in red using the “0” numeric format.

Result:

Example 3:

In this example the following custom format is used:

0.0; -0.000

If the number input in the cell is a positive number or zero, it will be printed with 1 decimal place. If the number is negative it will be printed with 3 decimal places.

Result:

–

One Semicolon With @ Sign <NUMERIC>;<TEXT>:

Similar to the previous section if one semicolon is used, then the custom formatting will be split in 2 sections. The section before the semicolon and the section after the semicolon. If an @ sign is used in the second section the following will apply:

Section 1: The custom formatting in this section will only apply to numeric values.
Section 2: The custom formatting in this section will apply to text values.

Result: <NUMERIC>;<TEXT>

For more information about the @ sign please see the article below:

Excel Custom Formatting, @ Character

I will explain this topic using an example.

Example:

Assume the following data is in column A:

We will use the following custom formatting:

0.000; "Pre" @

Basically what it means is:

If the data is numeric, use the 0.000 formatting
If the data is text use the “Pre” @ formatting

Result:

It can be seen that for the first 5 rows (which were numeric) the 0.000 has been applied. For rows 6 and 7 which were numeric the “Pre” @ formatting was applied.

–

Two Semicolons Without @ Sign, <POSITIVE>;<NEGATIVE>;<ZERO>:

If two semicolons are used then the custom formatting is split in 3 sections. A section before the first semicolon, a section between the 2 semicolons and a section after the second semicolon. If an @ sign is NOT used in the last section then the following applies:

Section 1: Formatting in this section will apply to positive numbers only
Section 2: Formatting in this section will apply to negative numbers only.
Section 3: Formatting in this section will apply to zero values only.

Result: <POSITIVE>;<NEGATIVE>;<ZERO>

This topic will be explained using examples.

Example 1:

Assume the same values as the previous example are printed in column A:

We will be applying the following custom formatting to the cells in column A:

"Pos" 0; "Neg" 0; "Zero"

As you can see the custom formatting has 3 parts:

Section 1:

"Pos" 0

This applies to positive numbers.

Section 2:

"Neg" 0

This applies to negative numbers.

Section 3:

"Zero"

This applies to zeros.

If the number is positive it is printed with the “0” numeric format with the text “Pos” before it. If it is negative it is printed with the “0” numeric format only this time the text “Neg” will be printed before it. If the value in the cell is zero then the text “zero” will be printed in the cell.

The result of using this custom formatting can be seen in the figure below:

Example 2:

In this example the formatting below is applied to the cells in column A:

[Blue]0;[Red]-0;[Green] "Zero"

If the number is is positive it is printed using the “0” numeric format in blue. If it is negative it is printed in the “0” numeric format only this time it is printed in red. If the number is zero then the text “zero” is printed in green in the cell.

Result:

–

Two Semicolons With @ Sign <POSITIVE and ZERO>;<NEGATIVE>;<TEXT>:

Similar to the previous sections if 2 semicolons are used the custom formatting is split in 3 sections. A section before the first semicolon, a section between the 2 semicolons and a section after the second semicolon. If an @ sign is used in the last section then the following applies:

Section 1: Formatting in this section will apply to positive numbers and zeros.
Section 2: Formatting in this section will apply to negative numbers only.
Section 3: Formatting in this section will apply to text values only.

Result:<POSITIVE and ZERO>;<NEGATIVE>;<TEXT>

For more information about the @ sign please see the article below:

Excel Custom Formatting, @ Character

I will explain this topic using an example.

Example:

Assume we have the following data in column A:

We will apply the following custom formatting to the values in column A:

[Blue]0;[Red]-0;"Pre" @

What this formatting means is:

If the value is positive or zero apply the [Blue] 0 formatting.
If the value is negative apply the [Red]-0 formatting.
If the value is text apply the “Pre” @ formatting.

–

Three Semicolons, <POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>:

If 3 semicolons are used then the custom formatting will have 4 sections:

Section 1: Formatting in this section will apply to positive numbers only.
Section 2: Formatting in this section will apply to negative numbers only.
Section 3: Formatting in this section will apply to zero values only.
Section 4: Formatting in this section will apply to text values only.

Result: <POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

I will explain this using examples

Example 1:

Assume we have the following data in column A:

We will be applying the following custom formatting to the cells:

"Pos" 0; "Neg" 0; "Zero";"Some Text"

It can be seen that the custom formatting has 3 semicolons therefore it comprises of 4 sections.

Section 1:

"Pos" 0

Applies to positive numbers.

Section 2:

"Neg" 0

Applies to negative numbers.

Section 3:

"Zero"

Applies to zeros.

Section 4:

"Some Text"

Applies to text values.

The first 3 sections are similar to before. The last section only applies to text values. If the user inputs a text value in the cell, then that text will be replaced with the text “Some Text”.

Result:

Example 2:

In this example I will use the custom formatting below:

[Blue]0;[Red]-0;[Green]"Zero";[Cyan]General

The first 3 sections are similar to before. If the value input in the cell is a text value then the custom formatting below will apply:

[Cyan]General

It will apply the general custom formatting to the text and change the color of the input text to cyan.

Result:

You can download the files related to this article from the links below: