Number.FromText
Reads a number from text, using the separators of the culture you name.
Number.FromText(text as nullable text, optional culture as nullable text) as nullable numberReturns: The number, or null when the input is null. An error when the text is not a valid number for that culture.
What it does
Number.FromText reads an amount typed as text. The meaning of "1,234" depends on the culture. In the US it is one thousand two hundred and thirty four. In much of Europe the same characters may be read as one point two three four, or refused, because the comma is the decimal mark. Passing the culture makes the reading explicit, so a German bank file and a US bank file are both read correctly.
Syntax and arguments
- text: the amount as text.
- culture (optional): a culture name, such as "en-US" for US formats or "de-DE" for German formats. Leave it out and the machine's culture is used, which is not a safe default for files from other countries.
Example
A bank in Germany sends amounts with a decimal comma and a thousands point, and a US bank sends them with a decimal point and a thousands comma. The team reads each feed with its own culture so the two sources give the same number for the same amount.
let
UsFeed = Number.FromText("1,234.50", "en-US"),
DeFeed = Number.FromText("1.234,50", "de-DE")
in
{UsFeed, DeFeed}
Both values come out as 1234.5. The text was different in each feed, but the number is the same once the culture is named.
Example: a second, different use
Read an amount with no thousands separator, such as "980,25", as a German amount, so the decimal comma is taken as the decimal mark.
Things to watch
- Always name the culture for files that come from another country. The machine's culture decides the answer otherwise.
- Remove currency signs and spaces before reading, with Text.Remove or Text.Trim. Unexpected characters can cause an error.
- Do not count on parentheses for negative amounts. Handle the sign yourself, as the bracket pattern on the Text.Remove page shows.
- A bad value gives an error. Wrap the step in try ... otherwise to set those rows aside.
In business terms
Amounts from banks, payment processors and foreign subsidiaries arrive with different separators. Reading each feed with the right culture is the difference between a consolidation that ties out and one that is off by a factor of a thousand.