Excel Number Format String
representation.file.excel_formatSpreadsheet custom number format code that defines how values are displayed. Found in metadata, headers, or as literal strings in spreadsheet data exports. Uses format tokens: # (optional digit), 0 (required digit), comma grouping, percent, date/time codes (yyyy, mm, dd, h, m, s), and optional multi-section syntax (positive;negative;zero;text).
Try a value
Excel Number Format String's rule accepts it.
Reads it as #,##0.00
Use it
finetype infer -i '#,##0.00' --mode column
# → representation.file.excel_formatSELECT ft_infer('#,##0.00');
-- → 'representation.file.excel_format'CAST({col} AS VARCHAR)Pattern
Length 1 to 100 characters.
^[#0.,;\[\]$€£¥%EeAaPpMm/dDyYhHsS ?:"\-+()<>=@*!\w]*$| Value | Length | Shape |
|---|---|---|
| #,##0.00 | 8 | matches |
| $#,##0.00 | 9 | matches |
| 0.00% | 5 | matches |
| mm/dd/yyyy | 10 | matches |
| h:mm:ss AM/PM | 13 | matches |
| 0.00E+00 | 8 | matches |
Similar types
Going the other way, this type's own rule accepts 5 of 5 samples of File Extension, 4 of 4 samples of File Size and 5 of 5 samples of MIME Type.
Background
Origin
Microsoft Excel lets a user create a custom number format by starting from a built-in number format and changing any of its code sections.[1] In Excel, a number format can have up to four sections of code, separated by semicolons.[1] Excel's four code sections define the format for positive numbers, negative numbers, zero values and text, in that order.[1]
Ecma International's page for ECMA-376 gives 29500 as the standard's ISO/IEC number.[2] In the Office Open XML spreadsheet format, a number format is a numFmt element whose formatCode attribute holds the number format code.[3] ISO/IEC 29500-1 describes the numFmt element as specifying number format properties which indicate how to format and render the numeric value of a cell.[3]
Use
In Excel's format codes, 0 is a digit placeholder that displays insignificant zeros if a number has fewer digits than there are zeros in the format.[1] Excel's # digit placeholder follows the same rules as 0, except that Excel does not display extra zeros.[1] Including the percent sign in an Excel number format displays numbers as a percentage of 100, so that .08 is displayed as 8%.[1] Excel's E codes, such as E+ in 0.00E+00, display a number in scientific (exponential) format.[1] Excel's documentation says the m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.[1] Excel lets a section of a format code start with one of eight colour names in square brackets, such as [Red].[1] Microsoft notes that the language and region settings on a computer may affect the custom formats that appear by default in Excel.[1]
openpyxl's documentation says dates and times can be stored in XLSX files either as an ISO 8601 formatted string or as a single number.[4] openpyxl recognises number formats such as [h]:mm:ss when reading XLSX files and returns datetime.timedelta values for them.[4] A format code describes how a value is shown, not the value itself, so keep it as text exactly as written rather than trying to parse it as a number or a date. (Meridian's view) Treat a lone m or mm in a format code as ambiguous until you have checked what sits either side of it. (Meridian's view)
Sources
- [1]Review guidelines for customizing a number formatdocumentation
- [2]ECMA-376 - Ecma Internationalcatalogue entry
- [3]NumberingFormat Class (DocumentFormat.OpenXml.Spreadsheet)documentation
- [4]Dates and Timesdocumentation
How it's detected
Common in GitTables data where format strings appear as column metadata. Not a data value itself but a format specification. VARCHAR passthrough since the format string should be preserved as-is.
Schema
https://meridian.online/schemas/representation.file.excel_format · Frictionless type string
finetype taxonomy representation.file.excel_format -o json-schema