Convert text to numbers and Unix timestamps to dates
Convert Column Type to Number pulls the number out of formatted text, and Number to Date reads Unix timestamps. When a value can't be converted, the cell becomes empty (NULL). For the task's steps and settings, see Convert text to dates and numbers.
Convert text to a number
Mammoth pulls the number out of formatted text. It strips a currency symbol in front of the number, comma thousand separators and a percent sign.
| Input type | Example | Result |
|---|---|---|
| Plain number | 1234 | 1234 |
| With decimals | 1234.56 | 1234.56 |
| Currency prefix | $1,234.56 | 1234.56 |
| Negative with currency | -$1,234.56 | -1234.56 |
| Negative (leading) | -1,234.56 | -1234.56 |
| Negative (after the symbol) | $-1,234.56 | -1234.56 |
| Percentage | 45% | 45 |
| Leading or trailing spaces | " $1,234.56 " | 1234.56 |
Currency symbols Mammoth recognises
These 5 symbols are recognised and stripped when they come before the number:
| Symbol | Currency | Example input | Result |
|---|---|---|---|
| $ | US Dollar | $1,234.56 | 1234.56 |
| € | Euro | €1,234.56 | 1234.56 |
| £ | British Pound | £1,234.56 | 1234.56 |
| ¥ | Japanese Yen / Chinese Yuan | ¥1234 | 1234 |
| ₹ | Indian Rupee | ₹1,234.56 | 1234.56 |
Thousand separators and European number formats
Mammoth removes comma thousand separators before converting. Standard grouping (1,234,567) and Indian-style grouping (12,34,567) both work. 1,234,567.89 and 1234567.89 both become 1234567.89.
European format isn't supported. Numbers that use a period as the thousand separator and a comma as the decimal (for example 1.234.567,89 or €1.234,56) aren't recognised and convert to empty. Use Find & Replace to change them to comma thousands and a period decimal first (1.234.567,89 → 1,234,567.89), then convert to Number.
Limits on converted numbers and timestamps
| Limit | Value |
|---|---|
| Decimal places stored after a text-to-number conversion | Up to 8 (NUMERIC(38,8)). 1234.56789012345 is stored as 1234.56789012. Numbers are rounded to 8 decimal places. |
| Currency symbols recognised | 5: $, €, £, ¥, ₹, in front of the number only |
| Valid Unix timestamp range (seconds) | -62,135,596,800 to 253,402,300,799 |
Convert a Unix timestamp to a date
When you convert a Number column to Date, Mammoth detects the time unit from the size of each value. You don't need to say which unit you have, because systems differ: JavaScript's Date.now() and Java's System.currentTimeMillis() return milliseconds (1735689600000), while Python's time.time() returns seconds (1735689600).
| Value range | Detected unit | How it's read | Example input | Result |
|---|---|---|---|---|
| -62,135,596,800 to 253,402,300,799 | Seconds | Unix timestamp (standard) | 1735689600 | 2025-01-01 00:00:00 |
| -62,135,596,800,000 to 253,402,300,799,000 | Milliseconds | Divided by 1,000 | 1735689600000 | 2025-01-01 00:00:00 |
| -62,135,596,800,000,000 to 253,402,300,799,000,000 | Microseconds | Divided by 1,000,000 | 1735689600000000 | 2025-01-01 00:00:00 |
| -62,135,596,800,000,000,000 to 253,402,300,799,000,000,000 | Nanoseconds | Divided by 1,000,000,000 | 1735689600000000000 | 2025-01-01 00:00:00 |
| Outside all ranges | NULL (invalid) | 99999999999999999999999 | (empty cell) |
Two edge cases: 0 is read as seconds and gives 1970-01-01 00:00:00 (the Unix epoch start). -86400 gives 1969-12-31 00:00:00, one day before the epoch.
Troubleshooting text to number conversions
| Problem | Cause | Fix |
|---|---|---|
| All cells become empty | No numbers found in the text | Check that the text contains numbers, not only words. |
| Some cells are empty | Unsupported currency symbol or format | Only €, $, ¥, £ and ₹ are recognised. |
| "1,234.56€" fails | Currency symbol after the number | Put the symbol in front: "€1,234.56". |
| "1.234,56" fails | European format | Use Find & Replace to make it "1,234.56", then convert. |
| "25%" gives 25 | The % sign is stripped | You get the number. Convert to Percentage or format the result as a percentage if needed. |
Troubleshooting Unix timestamp conversions
| Problem | Cause | Fix |
|---|---|---|
| All cells become empty | Values outside the valid timestamp range | Check the numbers. Valid range: -62,135,596,800 to 253,402,300,799 seconds. |
| Date is in 1970 when it should be 2025 | The unit was detected wrongly | Check the source system's convention (seconds or milliseconds). |
| Very old or future dates | Read as a different unit | Check the input really is a Unix timestamp. Other numbers convert wrongly. |
Related
- Convert text to dates and numbers: the Convert Column Type steps and settings.
- Convert text to a date with format codes: when the date is text.
- Column types, IDs, currency and percentages: what each type means and where it shows.
- Find and replace values: fix separators before converting.
Something wrong? support@mammoth.io