Share via

2.4.126 Format

  • Article

The Format record specifies a number format.


0


1


2


3


4


5


6


7


8


9

1
0


1


2


3


4


5


6


7


8


9

2
0


1


2


3


4


5


6


7


8


9

3
0


1

ifmt

stFormat (variable)

...

ifmt (2 bytes):  An IFmt structure that specifies the identifier of the format string specified by stFormat. The value of ifmt.ifmt SHOULD<91> be a value within one of the following ranges. The value of ifmt.ifmt MUST be a value within one of the following ranges or within 383 to 392.

  • 5 to 8

  • 23 to 26

  • 41 to 44

  • 63 to 66

  • 164 to 382

stFormat (variable):  An XLUnicodeString structure that specifies the format string for this number format. The format string indicates how to format the numeric value of the cell. The length of this field MUST be greater than or equal to 1 character and less than or equal to 255 characters. For more information about how format strings are interpreted, see [ECMA-376] Part 4: Markup Language Reference, section 3.8.31.

For a string to be considered a valid format string, it MUST be well-formed according to the following ABNF specification.

ABNF Grammar for number format strings

These definitions are for en-US locale. International consideration as specified in [ECMA-376] Part 4: Markup Language Reference, section 3.8.31 MUST be accounted for and the ABNF MUST be modified accordingly for specific international number formats.

The following rules are not expressed in the ABNF grammar, but apply to the grammar:

  1. In the following ABNF specification, the following tokens in the first table that follows can occur 0 or more times anywhere in the grammar, as long as they do not break apart the elements in the second table that follows:

    Token

    LITERAL-STRING

    LITERAL-CHAR-REPEAT

    LITERAL-CHAR-SPACE

    Elements

    INTL*

    LITERAL*

    NFDateTimeToken

    NFPartExponential

    NFPartCond

    NFPartLocaleID

    NFPartColor

    NFPartIntNum

    NFPartStrColor

    Elements

    INTL*

    LITERAL*

    NFDateTimeToken

    NFPartExponential

    NFPartCond

    NFPartLocaleID

    NFPartColor

    NFPartIntNum

    NFPartStrColor

  2. The following token MUST occur 0 or 1 times in each section as defined in [ECMA-376] Part 4: Markup Language Reference, section 3.8.31.

NFPartLocaleID

NFAbsTimeToken MUST occur 0 or 1 times in NFDateTime. 

An absolute time token, such as NFPartAbsHour, MUST NOT coexist with a non-absolute equivalent token, such as NFPartHour, in NFDateTime.

Following is the ABNF grammar for number format strings.

All = ([NFPartColor] NFPartCond NFGeneral) / NFAnyNoCond / (NFAnyNoText ASCII-SEMICOLON NFAny) / (NFAnyNoText ASCII-SEMICOLON NFAnyNoText ASCII-SEMICOLON NFAnyNoCond) / (NFAnyNoText ASCII-SEMICOLON NFAnyNoText ASCII-SEMICOLON NFAnyNoTextNoCond ASCII-SEMICOLON [NFText / NFGeneral])

NFAny = [NFPartColor] (([NFPartCond] NFNumber) / NFText / ([NFPartCond] NFFraction) / ([NFPartCond] [NFDateTime] [NFGeneral] [NFDateTime]))

NFAnyNoText = [NFPartColor] [NFPartCond] (NFNumber / NFFraction / ([NFDateTime] [NFGeneral] [NFDateTime]))

NFAnyNoCond = [NFPartColor] (NFNumber / NFText / NFFraction / ([NFDateTime] [NFGeneral] [NFDateTime]))

NFAnyNoTextNoCond = [NFPartColor] (NFNumber / NFFraction / ([NFDateTime] [NFGeneral] [NFDateTime]))

NFGeneral = INTL-NUMFMT-GENERAL

NFNumber = NFPartNum [NFPartExponential NFPartNum] *INTL-CHAR-NUMGRP-SEP *INTL-AMPM

NFDateTimeToken = NFPartYear / NFPartMonth / NFPartDay / NFPartHour / NFPartMinute / NFPartSecond / NFAbsTimeToken

NFAbsTimeToken = NFPartAbsHour / NFPartAbsSecond / NFPartAbsMinute

NFDateTime = *INTL-AMPM (1*(NFDateTimeToken) *(NFDateTimeToken / NFPartSubSecond / INTL-CHAR-DATE-SEP / INTL-CHAR-TIME-SEP / INTL-AMPM))

NFText = (1*ASCII-COMMERCIAL-AT *(ASCII-COMMERCIAL-AT / INTL-AMPM)) / (*(ASCII-COMMERCIAL-AT / INTL-AMPM) 1*ASCII-COMMERCIAL-AT)

NFFraction = NFPartFraction ASCII-SOLIDUS NFPartFraction [NFPartNum] *INTL-AMPM

NFPartNum = 1*NFPartNumToken2 *(NFPartNumToken2 / ASCII-PERCENT-SIGN)) / (*(NFPartNumToken2 / ASCII-PERCENT-SIGN) 1*NFPartNumToken2)

NFPartExponential = ASCII-CAPITAL-LETTER-E NFPartSign

NFPartYear = 2(ASCII-SMALL-LETTER-Y) / 4(ASCII-SMALL-LETTER-Y)

NFPartMonth = 1*5(ASCII-SMALL-LETTER-M)

NFPartDay = 1*4(ASCII-SMALL-LETTER-D)

NFPartHour = 1*2(ASCII-SMALL-LETTER-H)

NFPartAbsHour = ASCII-LEFT-SQUARE-BRACKET 1*ASCII-SMALL-LETTER-H ASCII-RIGHT-SQUARE-BRACKET

NFPartMinute = 1*2(ASCII-SMALL-LETTER-M)

NFPartAbsMinute = ASCII-LEFT-SQUARE-BRACKET 1*ASCII-SMALL-LETTER-M ASCII-RIGHT-SQUARE-BRACKET

NFPartSecond = 1*2(ASCII-SMALL-LETTER-S)

NFPartAbsSecond = ASCII-LEFT-SQUARE-BRACKET 1*ASCII-SMALL-LETTER-S ASCII-RIGHT-SQUARE-BRACKET

NFPartSubSecond = INTL-CHAR-DECIMAL-SEP 1*3ASCII-DIGIT-ZERO

NFPartCond = ASCII-LEFT-SQUARE-BRACKET NFPartCompOper NFPartCondNum ASCII-RIGHT-SQUARE-BRACKET

NFPartCompOper = (ASCII-LESS-THAN-SIGN [ASCII-EQUALS-SIGN / ASCII-GREATER-THAN-SIGN]) / ASCII-EQUALS-SIGN / (ASCII-GREATER-THAN-SIGN [ASCII-EQUALS-SIGN])

NFPartLocaleID = ASCII-LEFT-SQUARE-BRACKET ASCII-DOLLAR-SIGN 1*UTF16-ANY [ASCII-HYPHEN-MINUS 3*8ASCII-DIGIT-HEXADECIMAL] ASCII-RIGHT-SQUARE-BRACKET

NFPartCondNum = [ASCII-HYPHEN-MINUS] NFPartIntNum [INTL-CHAR-DECIMAL-SEP NFPartIntNum] [NFPartExponential NFPartIntNum]

NFPartSign = ASCII-PLUS-SIGN / ASCII-HYPHEN-MINUS

NFPartColor = ASCII-LEFT-SQUARE-BRACKET INTL-COLOR / (NFPartStrColor NFPart1To56) ASCII-RIGHT-SQUARE-BRACKET

NFPart1To56 = NFPartNumber1To9 / NFPartNumber1To4 ASCII-DIGIT / ASCII-DIGIT-FIVE (ASCII-DIGIT-ZERO / NFPartNumber1To6)

NFPartIntNum = 1*ASCII-DIGIT

NFPartNumToken1 = ASCII-NUMBER-SIGN / ASCII-QUESTION-MARK / ASCII-DIGIT-ZERO

NFPartNumToken2 = NFPartNumToken1 / INTL-CHAR-DECIMAL-SEP / INTL-CHAR-NUMGRP-SEP

NFPartFraction = (1*NFPartIntNum *(NFPartIntNum / ASCII-PERCENT-SIGN)) / (*(NFPartIntNum / ASCII-PERCENT-SIGN) 1*NFPartIntNum) / (1*NFPartNumToken1 *(NFPartNumToken1 / ASCII-PERCENT-SIGN)) / (*(NFPartNumToken1 / ASCII-PERCENT-SIGN) 1*NFPartNumToken1)

NFPartNumber1To4 = ASCII-DIGIT-ONE / ASCII-DIGIT-TWO / ASCII-DIGIT-THREE / ASCII-DIGIT-FOUR

NFPartNumber1To6 = NFPartNumber1To4 / ASCII-DIGIT-FIVE / ASCII-DIGIT-SIX

NFPartNumber1To9 = NFPartNumber1To6 / ASCII-DIGIT-SEVEN / ASCII-DIGIT-EIGHT / ASCII-DIGIT-NINE

NFPartStrColor = ASCII-CAPITAL-LETTER-C ASCII-SMALL-LETTER-O ASCII-SMALL-LETTER-L ASCII-SMALL-LETTER-O ASCII-SMALL-LETTER-R

LITERAL-CHAR = ASCII-REVERSE-SOLIDUS UTF16-ANY

LITERAL-CHAR-REPEAT = ASCII-ASTERISK UTF16-ANY

LITERAL-STRING = (ASCII-QUOTATION-MARK 1*UTF16-ANY-WITHOUT-QUOTE ASCII-QUOTATION-MARK) / 1*LITERAL-CHAR

UTF16-ANY-WITHOUT-QUOTE = %x0000-0021 / %x0023-FFFF

LITERAL-CHAR-SPACE = ASCII-LOW-LINE UTF16-ANY

INTL-CHAR-DECIMAL-SEP = ASCII-FULL-STOP

INTL-CHAR-NUMGRP-SEP = ASCII-COMMA

INTL-CHAR-DATE-SEP = ASCII-SOLIDUS

INTL-CHAR-TIME-SEP = ASCII-COLON

INTL-COLOR = (ASCII-CAPITAL-LETTER-B ASCII-SMALL-LETTER-L ASCII-SMALL-LETTER-A ASCII-SMALL-LETTER-C ASCII-SMALL-LETTER-K) / (ASCII-CAPITAL-LETTER-B ASCII-SMALL-LETTER-L ASCII-SMALL-LETTER-U ASCII-SMALL-LETTER-E) / (ASCII-CAPITAL-LETTER-C ASCII-SMALL-LETTER-Y ASCII-SMALL-LETTER-A ASCII-SMALL-LETTER-N) / (ASCII-CAPITAL-LETTER-G ASCII-SMALL-LETTER-R ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-N) / (ASCII-CAPITAL-LETTER-M ASCII-SMALL-LETTER-A ASCII-SMALL-LETTER-G ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-N ASCII-SMALL-LETTER-T ASCII-SMALL-LETTER-A) / (ASCII-CAPITAL-LETTER-R ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-D ) / (ASCII-CAPITAL-LETTER-W ASCII-SMALL-LETTER-H ASCII-SMALL-LETTER-I ASCII-SMALL-LETTER-T ASCII-SMALL-LETTER-E) / (ASCII-CAPITAL-LETTER-Y ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-L ASCII-SMALL-LETTER-L ASCII-SMALL-LETTER-O ASCII-SMALL-LETTER-W)

INTL-NUMFMT-GENERAL = ASCII-CAPITAL-LETTER-G ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-N ASCII-SMALL-LETTER-E ASCII-SMALL-LETTER-R ASCII-SMALL-LETTER-A ASCII-SMALL-LETTER-L

INTL-AMPM = (ASCII-CAPITAL-LETTER-A ASCII-CAPITAL-LETTER-M ASCII-SOLIDUS ASCII-CAPITAL-LETTER-P ASCII-CAPITAL-LETTER-M) / "A/P"

UTF16-ANY = %x0000-FFFF

ASCII-SPACE = %x20

ASCII-EXCLAMATION-MARK = %x21

ASCII-QUOTATION-MARK = %x22

ASCII-NUMBER-SIGN = %x23

ASCII-DOLLAR-SIGN = %x24

ASCII-PERCENT-SIGN = %x25

ASCII-AMPERSAND = %x26

ASCII-APOSTROPHE = %x27

ASCII-LEFT-PARENTHESIS = %x28

ASCII-RIGHT-PARENTHESIS = %x29

ASCII-ASTERISK = %x2A

ASCII-PLUS-SIGN = %x2B

ASCII-COMMA = %x2C

ASCII-HYPHEN-MINUS = %x2D

ASCII-FULL-STOP = %x2E

ASCII-SOLIDUS = %x2F

ASCII-DIGIT-ZERO = %x30

ASCII-DIGIT-ONE = %x31

ASCII-DIGIT-TWO = %x32

ASCII-DIGIT-THREE = %x33

ASCII-DIGIT-FOUR = %x34

ASCII-DIGIT-FIVE = %x35

ASCII-DIGIT-SIX = %x36

ASCII-DIGIT-SEVEN = %x37

ASCII-DIGIT-EIGHT = %x38

ASCII-DIGIT-NINE = %x39

ASCII-COLON = %x3A

ASCII-SEMICOLON = %x3B

ASCII-LESS-THAN-SIGN = %x3C

ASCII-EQUALS-SIGN = %x3D

ASCII-GREATER-THAN-SIGN = %x3E

ASCII-QUESTION-MARK = %x3F

ASCII-COMMERCIAL-AT = %x40

ASCII-CAPITAL-LETTER-A = %x41

ASCII-CAPITAL-LETTER-B = %x42

ASCII-CAPITAL-LETTER-C = %x43

ASCII-CAPITAL-LETTER-D = %x44

ASCII-CAPITAL-LETTER-E = %x45

ASCII-CAPITAL-LETTER-F = %x46

ASCII-CAPITAL-LETTER-G = %x47

ASCII-CAPITAL-LETTER-H = %x48

ASCII-CAPITAL-LETTER-I = %x49

ASCII-CAPITAL-LETTER-J = %x4A

ASCII-CAPITAL-LETTER-K = %x4B

ASCII-CAPITAL-LETTER-L = %x4C

ASCII-CAPITAL-LETTER-M = %x4D

ASCII-CAPITAL-LETTER-N = %x4E

ASCII-CAPITAL-LETTER-O = %x4F

ASCII-CAPITAL-LETTER-P = %x50

ASCII-CAPITAL-LETTER-Q = %x51

ASCII-CAPITAL-LETTER-R = %x52

ASCII-CAPITAL-LETTER-S = %x53

ASCII-CAPITAL-LETTER-T = %x54

ASCII-CAPITAL-LETTER-U = %x55

ASCII-CAPITAL-LETTER-V = %x56

ASCII-CAPITAL-LETTER-W = %x57

ASCII-CAPITAL-LETTER-X = %x58

ASCII-CAPITAL-LETTER-Y = %x59

ASCII-CAPITAL-LETTER-Z = %x5A

ASCII-LEFT-SQUARE-BRACKET = %x5B

ASCII-REVERSE-SOLIDUS = %x5C

ASCII-RIGHT-SQUARE-BRACKET = %x5D

ASCII-CIRCUMFLEX-ACCENT = %x5E

ASCII-LOW-LINE = %x5F

ASCII-GRAVE-ACCENT = %x60

ASCII-SMALL-LETTER-A = %x61

ASCII-SMALL-LETTER-B = %x62

ASCII-SMALL-LETTER-C = %x63

ASCII-SMALL-LETTER-D = %x64

ASCII-SMALL-LETTER-E = %x65

ASCII-SMALL-LETTER-F = %x66

ASCII-SMALL-LETTER-G = %x67

ASCII-SMALL-LETTER-H = %x68

ASCII-SMALL-LETTER-I = %x69

ASCII-SMALL-LETTER-J = %x6A

ASCII-SMALL-LETTER-K = %x6B

ASCII-SMALL-LETTER-L = %x6C

ASCII-SMALL-LETTER-M = %x6D

ASCII-SMALL-LETTER-N = %x6E

ASCII-SMALL-LETTER-O = %x6F

ASCII-SMALL-LETTER-P = %x70

ASCII-SMALL-LETTER-Q = %x71

ASCII-SMALL-LETTER-R = %x72

ASCII-SMALL-LETTER-S = %x73

ASCII-SMALL-LETTER-T = %x74

ASCII-SMALL-LETTER-U = %x75

ASCII-SMALL-LETTER-V = %x76

ASCII-SMALL-LETTER-W = %x77

ASCII-SMALL-LETTER-X = %x78

ASCII-SMALL-LETTER-Y = %x79

ASCII-SMALL-LETTER-Z = %x7A

ASCII-LEFT-CURLY-BRACKET = %x7B

ASCII-VERTICAL-LINE = %x7C

ASCII-RIGHT-CURLY-BRACKET = %x7D

ASCII-TILDE = %x7E

ASCII-DELETE = %x7F

ASCII-CRLF = %x0d.0a

ASCII-DIGIT = ASCII-DIGIT-ZERO / ASCII-DIGIT-ONE / ASCII-DIGIT-TWO / ASCII-DIGIT-THREE / ASCII-DIGIT-FOUR / ASCII-DIGIT-FIVE / ASCII-DIGIT-SIX / ASCII-DIGIT-SEVEN / ASCII-DIGIT-EIGHT / ASCII-DIGIT-NINE

ASCII-DIGIT-HEXADECIMAL = ASCII-DIGIT / ASCII-SMALL-LETTER-A / ASCII-SMALL-LETTER-B / ASCII-SMALL-LETTER-C / ASCII-SMALL-LETTER-D / ASCII-SMALL-LETTER-E / ASCII-SMALL-LETTER-F / ASCII-CAPITAL-LETTER-A / ASCII-CAPITAL-LETTER-B / ASCII-CAPITAL-LETTER-C / ASCII-CAPITAL-LETTER-D / ASCII-CAPITAL-LETTER-E / ASCII-CAPITAL-LETTER-F