Convert Type

Turn a text column into real numbers or real dates. Or turn a date into text.

Why you need it

A CSV file carries no type information. Every column arrives as text unless the app can read it as something else. A sales export often lands with $1,200.00 in the amount column and 15/03/2026 in the date column.

Text will not add up, and text will not sort by month. Convert Type fixes both problems. It offers three targets: text, numeric and date.

Convert one column

1. Type convert in the Search transforms box, in the Pipeline panel.
2. Select Convert Type in the results.
3. Open Select column and pick the column to fix.
4. Open Convert to and choose text, numeric or date.
5. Check the Preview block below the form.
6. Click Apply.

The column keeps its name and its place in the grid. Only its type changes.

Text to numbers: the cleaning is automatic

Choose numeric on a text column, and the step strips the formatting first. You do not have to clean it yourself.

The step also removes the euro, pound and rupee signs, and any spaces around the number.

Text to dates: auto-detect

Choose date, and a checkbox called Auto-detect date format appears. It is on already. Auto-detect tries a long list of common layouts on your values.

The list covers year-first dates such as 2026-03-15. It covers month-first dates such as 03/15/2026. It covers day-first dates such as 15/03/2026. It covers written months such as 15 Mar 2026. Times, including 10:30 AM and a trailing Z, are handled too.

The format is decided once for the whole column, not row by row. In a column holding 01/02/2026 and 25/12/2026, the second value can only be day-first, so the first one is read as 1 February and not as 5 January. One column, one calendar.

Leave the checkbox on for most files. Watch the preview to confirm the result.

Text to dates: state the format yourself

Auto-detect can only work with the evidence the column carries. A column where every day is 12 or lower has none, so state the layout yourself in two steps.

1. Clear the Auto-detect date format checkbox.
2. Type your layout in Date format in the data. For day-first dates, type %d/%m/%Y. The box offers the common patterns as a list, so you rarely have to remember them.

Useful codes: %d is the day and %m is the month number. %Y is the four-digit year, and %b is a short month name.

Date to text

Pick a date column, then choose text. A box called Date to text format appears, with the common patterns offered as a list. Type the layout you want to read. Type %b %Y to get Mar 2026. Leave the box empty for the default layout.

Number to text: decimals, commas, a currency sign

Turning a number back into text used to give you whatever the database wrote, so 500 came out as 500.0. Pick a number column and choose text, and four boxes appear to say how it should read:

• Decimal places fixes how many digits follow the point. Leave it empty to write the number as it is stored.
• Group thousands with commas turns 1000000 into 1,000,000.
• Prefix and Suffix put text on either side: a $ in front, a % behind.

A blank cell stays blank through all of it. Set none of the four and you get the plain conversion, exactly as before.

This is a display format, so the result is text. Do the arithmetic first and format last, or keep the number in one column and the formatted text in another with Apply results into.

Values that will not convert

Nothing breaks and nothing is dropped. A value that cannot become a number or a date goes empty. The other rows convert as normal. Look for empty cells in the grid after you apply.

Add a Filter step with the is Empty comparison to list the failures.

For SQL users

Text to numeric runs the clean-up inline, then casts:

SELECT "order_id", TRY_CAST(REGEXP_REPLACE(REGEXP_REPLACE(TRIM(CAST("amount" AS VARCHAR)), '^\((.*)\)$', '-\1'), '[,$€£₹ %]', '', 'g') AS DOUBLE) AS "amount", "order_date" FROM data

Number to text with a format is DuckDB's format, with the prefix and suffix concatenated on. || keeps a NULL a NULL, which is why a blank cell stays blank:

SELECT ('$' || format('{:,.2f}', CAST("amount" AS DOUBLE))) AS "amount" FROM data

Related Operations

• Update Values - Update column values with expression
• Find & Replace - Search and replace values in a column
• Text Transform - Transform text: uppercase, lowercase, trim