# Excel Number Format String

Key: `representation.file.excel_format`

Spreadsheet 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).

Page: <https://meridian.online/docs/finetype/types/representation/file/excel_format>

Samples: `#,##0.00`, `$#,##0.00`, `0.00%`, `mm/dd/yyyy`, `h:mm:ss AM/PM`, `0.00E+00`

## Use it

Shell

```shell
finetype infer -i '#,##0.00' --mode column
# → representation.file.excel_format
```

DuckDB

```sql
SELECT ft_infer('#,##0.00');
-- → 'representation.file.excel_format'
```

Cast template: {col} is your column

```sql
CAST({col} AS VARCHAR)
```

## 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]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5) In Excel, a number format can have up to four sections of code, separated by semicolons.[[1]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5) Excel's four code sections define the format for positive numbers, negative numbers, zero values and text, in that order.[[1]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5)

Ecma International's page for ECMA-376 gives 29500 as the standard's ISO/IEC number.[[2]](https://ecma-international.org/publications-and-standards/standards/ecma-376/) In the Office Open XML spreadsheet format, a number format is a numFmt element whose formatCode attribute holds the number format code.[[3]](https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat) 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]](https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat)

### 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]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5) Excel's \# digit placeholder follows the same rules as 0, except that Excel does not display extra zeros.[[1]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5) Including the percent sign in an Excel number format displays numbers as a percentage of 100, so that .08 is displayed as 8%.[[1]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5) Excel's E codes, such as E+ in 0.00E+00, display a number in scientific (exponential) format.[[1]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5) 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]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5) Excel lets a section of a format code start with one of eight colour names in square brackets, such as \[Red\].[[1]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5) Microsoft notes that the language and region settings on a computer may affect the custom formats that appear by default in Excel.[[1]](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5)

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]](https://openpyxl.readthedocs.io/en/stable/datetime.html) openpyxl recognises number formats such as \[h\]:mm:ss when reading XLSX files and returns datetime.timedelta values for them.[[4]](https://openpyxl.readthedocs.io/en/stable/datetime.html) 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 format](https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5) — documentation
2. [ECMA-376 - Ecma International](https://ecma-international.org/publications-and-standards/standards/ecma-376/) — catalogue entry
3. [NumberingFormat Class (DocumentFormat.OpenXml.Spreadsheet)](https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat) — documentation
4. [Dates and Times](https://openpyxl.readthedocs.io/en/stable/datetime.html) — documentation

## 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.