Documentation

    Number

    Convert numbers stored as formatted text into numeric values you can calculate with, sort and compare. Choose the decimal and thousands separators used by your source, such as 1,234.56 or 1.234,56.

    Parse amounts

    Manual Input has four rows with id and amount columns. Number reads amount using European separators and adds parsed_number as a Double.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    idamountparsed_number
    11.234,561234.56
    2-2.000,90-2000.9
    3pending
    4
    1. Change Output as to Bigint. 1.234,56 becomes 1234, and -2.000,90 becomes -2000. Fractions are dropped toward zero.
    2. Change Output column to amount. The numeric values replace the original text instead of adding a column.
    3. Change If parse fails to Fail. The value pending causes an error.

    When to use

    Use Number when amounts are stored as formatted text, such as 1.234,56 or 1,200, and you need to calculate with them.

    Read the output

    The output keeps id and amount and adds parsed_number. 1.234,56 becomes 1234.56, and -2.000,90 becomes -2000.9. These are numeric values. The text pending and the missing value produce NULL. All four rows stay.

    Bigint removes the fractional part: 1234.56 becomes 1234, and -2000.9 becomes -2000. It doesn't round to the nearest integer.

    Choose separators based on the source. For example, 1,234 means one thousand two hundred thirty-four in US format but 1.234 in EU format. Parsing without an error doesn't mean you chose the right separators.

    To infer types for unformatted numeric text automatically, use Auto Field.

    To remove currency symbols or other text before parsing, use RegEx.

    Inputs and outputs

    Inputs

    1 port
    InRequired

    Accepts: Tables, Geospatial data.

    Accepts one connection on this port.

    Outputs

    1 port
    Out

    Configure the block

    Choose how the source numbers are written and whether to keep their fractional part.

    Column

    Choose the text column to parse. The first text column is selected automatically.

    Text looks like

    Choose Plain for 1234.56, US for 1,234.56, EU for 1.234,56 or Custom to set your own separators. Default: US (1,234.56).

    Thousands

    Shown for Custom. Enter one grouping character, such as a space, or leave blank for no grouping.

    Decimal

    Shown for Custom. Enter one decimal character, or leave blank to accept only integers. It must differ from the thousands separator.

    Output as

    Double and Float keep fractional values. Decimal keeps exact digits at a fixed scale. Bigint and Integer drop the fractional part toward zero without rounding. Default: Double.

    Decimal places

    Digits kept after the decimal point. Values round to this many places.

    Total digits

    Maximum digits in the value, including decimal places. Values that don't fit become NULL.

    Output column

    Name the result column. Use the source column's name to replace it. Default: parsed_number.

    If parse fails

    Null leaves an invalid value missing while keeping its row. Fail reports an error instead. Default: Null.

    Number FAQs

    Can it parse currency symbols or percentages?
    Symbols such as $ and % aren't removed automatically. Remove them before parsing. Removing % from 25% produces 25; Number doesn't scale it to 0.25.
    Does it check thousands grouping?
    Yes. In US format, 1,234 is valid but 1,23 isn't. Ungrouped numbers such as 1234 are also accepted.
    What happens to missing values?
    NULL values stay NULL, even with Fail selected. Invalid text becomes NULL with Null selected, or causes an error with Fail selected.

    Prepare your data. Build it in Strata.

    Strata opens in your browser, with no account.