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.
Number interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| id | amount | parsed_number |
|---|---|---|
| 1 | 1.234,56 | 1234.56 |
| 2 | -2.000,90 | -2000.9 |
| 3 | pending | |
| 4 |
- Change Output as to
Bigint.1.234,56becomes1234, and-2.000,90becomes-2000. Fractions are dropped toward zero. - Change Output column to
amount. The numeric values replace the original text instead of adding a column. - Change If parse fails to
Fail. The valuependingcauses 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.