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: Try Convert Type on its own

Click the green + in the Pipeline panel and select Convert Type from the Transform group. Pick the revenue column and set the target type to numeric. The dropdown offers three broad choices, text, numeric and date, and numeric maps to DOUBLE.

Converting text to number strips currency symbols, thousands separators, spaces and percent signs before it casts, and reads a parenthesised value as negative. So $1,234.56 lands as 1234.56, €45.00 as 45, £1,200 as 1200 and (500.00) as -500. There is no need to strip dollar signs or commas by hand first.

One thing to know about percentages: 5% becomes 5, not 0.05. The percent sign is deleted, not divided out. If you want a rate, add a Math step afterwards that divides the column by 100.

Under the hood ExploreMyData scrubs the formatting and then uses TRY_CAST instead of a hard CAST. The difference matters: if a value still can't be converted (a stray "N/A" or "FREE"), TRY_CAST returns NULL for that row instead of failing the whole query. The column also stays where it was in the grid rather than jumping to the far right.

For most files that is the entire job. Look at the converted column and check two things: rows that went blank, and rows whose number looks wrong. Blanks mean a format the built-in cleanup doesn't recognise, such as 29.99 USD. Wrong numbers mean European punctuation: 1.234,56 loses its comma and comes out as 1.23456, which is quietly incorrect rather than obviously broken. Either way, step 2 is the fallback.

Step 2 (fallback): clean the text by hand

Delete the Convert Type step first. Once the column is DOUBLE the original text is gone, so there is nothing left to clean. Pipeline steps are deletable, so remove it, add the replacements below, then add Convert Type back at the end.

Open Find & Replace from the Transform group and select the revenue column. It matches substrings, and "Case sensitive" is on by default, which is what you want here.

Trailing currency codes. Find USD including the leading space, and leave "replace" empty. The SQL: REPLACE(revenue, ' USD', ''). Worth knowing: replacing with nothing produces an empty string, not a NULL, so a row that held only a currency code ends up as '' rather than missing.

European punctuation. Order matters. First find . and replace with nothing, which drops the thousands separator. Then find , and replace with . to turn the decimal comma into a decimal point. "1.234,56" becomes "1234.56". Run those two the other way round and you get nonsense.

Each replacement is its own pipeline step, so you can see exactly what was cleaned and delete one later without redoing the rest.

What you get back

After the conversion the column type is 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. One pipeline step, or three in the awkward cases, 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)". Convert Type handles this for you: a value wrapped in parentheses comes out negative, so no Find & Replace steps and no manual sign fixing.

Mixed formats in one column. If some rows have "$100" and others have "100 EUR", the dollar rows convert cleanly and the EUR rows go blank. Strip the currency codes with Find & Replace first, then convert everything in one pass. The currency information is lost, so if you need it, use Copy Columns to keep the original text alongside the number.

Values you never want as numbers. Invoice numbers, ZIP codes and account IDs look numeric but should stay text. Converting them drops leading zeros and turns long IDs into floats. Leave those columns alone.

The full pipeline

For a normal dollar-and-comma column, one step:

1. Convert Type: revenue to numeric

For trailing currency codes or European punctuation, three:

1. Find & Replace: strip USD, or strip .
2. Find & Replace: turn , into . (European only)
3. Convert Type: revenue to numeric

Whichever route you took, the pipeline is saved. Reload a fresh file in the same format and the same steps replay against it.

Fix your currency columns →