Converting "$1,234.56" Strings to Actual Numbers

You load a sales report and try to sum the revenue column. Nothing happens. You try to filter for orders over $100. No results. You try to sort by price. The sort order is wrong - "$9.99" appears after "$80.00" because it's sorting alphabetically, not numerically.

The problem: your "price" column contains strings like "$1,234.56", not numbers. The dollar sign and commas are human-friendly formatting, but DuckDB reads the whole thing as text. You can't do math on text.

Why this happens

CSV files don't have data types. Everything is text until a tool decides to parse it. Most import tools will correctly read "1234.56" as a number, but the moment there's a dollar sign or comma, it stays as a string. Same goes for euro signs, pound signs, percent symbols, or parentheses used to indicate negative numbers.

Common offenders:

• $1,234.56 - dollar sign + thousand separator
• €45.00 - euro symbol
• £1,200 - pound sign
• (500.00) - accounting format for negative numbers
• 29.99 USD - trailing currency code

Step 1: Strip the currency symbols

Open Find & Replace from the Transform group. Select the revenue column. Set "find" to $ and leave "replace" empty. Apply it. Every dollar sign disappears.

The SQL: REPLACE(revenue, '$', '')

If your data has mixed currencies, run this step once for each symbol. Strip the euro sign next: find €, replace with nothing. Then the pound sign. Each one is a separate pipeline step, which makes it easy to see exactly what was cleaned.

Step 2: Remove the commas

The values now look like "1,234.56" - closer, but that comma is still a problem. Run Find & Replace again. Find , and replace with nothing.

Now you have "1234.56". Pure numeric characters and a decimal point. This is what we need.

The combined SQL at this point: REPLACE(REPLACE(revenue, '$', ''), ',', '')

Step 3: Convert to a number

The column still has type VARCHAR. It just happens to contain numeric-looking text. Use Convert Type from the Transform group. Select the column and choose DOUBLE as the target type.

ExploreMyData uses TRY_CAST(revenue AS DOUBLE) instead of a hard CAST. The difference matters: if any value can't be converted (maybe there's a stray "N/A" or "FREE" in the column), TRY_CAST returns NULL for that row instead of crashing the whole query.

After this step, the column type changes to DOUBLE. Numbers are real numbers. You can sum them, average them, filter by range, sort numerically. "$9.99" no longer sorts after "$80.00".

Now you can calculate

With actual numbers, the Math operation unlocks. Some things you can do:

• Add a "tax" column: revenue * 0.08
• Calculate profit margin: (revenue - cost) / revenue
• Round to whole dollars: use the Math operation with rounding

None of this was possible when the column was text. Three pipeline steps turned dead strings into live data.

Handling edge cases

A few things to watch for:

Negative numbers in parentheses. Accounting exports sometimes show -500 as "(500.00)". Strip the parentheses with two Find & Replace steps (one for the opening paren, one for the closing), then you'll need to handle the sign separately. This is uncommon enough that you might just flag those rows manually.

European number formats. In Germany and France, the decimal separator is a comma and the thousands separator is a period: "1.234,56" instead of "1,234.56". You'll need to strip the periods first, then replace the comma with a period, then convert. The order matters.

Mixed formats in one column. If some rows have "$100" and others have "100 EUR", you'll want to standardize the format before converting. Strip all known currency symbols and codes, then convert. The currency information is lost, but you can always add it back as a separate column.

The full pipeline

Three steps. That's it:

1. Find & Replace: strip $
2. Find & Replace: strip ,
3. Convert Type: VARCHAR to DOUBLE

Next time you load updated data with the same format, the pipeline runs the same steps automatically. No re-cleaning required.

Fix your currency columns →