---
title: "Clean messy values with Cleanse in Strata | Oplema"
description: "Clean messy values with Cleanse: trim spaces, change case, remove letters, digits or punctuation, round decimals and override settings per column."
url: "https://oplema.com/docs/strata/prep/cleanse"
updated: "2026-11-04"
---

# Cleanse

Clean up messy values before you compare or analyze them. Trim text, fix capitals and round decimals. Settings apply across columns, and you can override them for a single column.

**Learn this in context:** [Clean up inconsistent text in a CSV](https://oplema.com/learn/export-to-report/clean-up-text) (Build a report from a CSV export); [Find out why rows don't match in a join](https://oplema.com/learn/many-sources/find-unmatched-join-rows) (Combine and reconcile data files)

## Clean names and amounts

Manual Input has four rows with name and amount. Cleanse starts with Trim Whitespace, Collapse Whitespace and Title case on, so the padded, unevenly spaced ada lovelace becomes Ada Lovelace.

Result
| name | amount |
| --- | --- |
| Ada Lovelace | 12.346 |
| Lin Chen | 8.764 |
|  | 5.432 |
| Sam! |  |

1. Expand Decimal Columns, select Round Values and set Round Decimals to `2`. The amounts become 12.35, 8.76, 5.43 and NULL.
2. Under Text Columns, select Remove Punctuation. `Sam!` becomes `Sam`; the other names stay unchanged.
3. Expand Null Values and select Remove Null Rows. Only Ada Lovelace and Lin Chen remain, because the other two rows have a missing name or amount.

## When to use

Use Cleanse before comparing or grouping values, when stray spaces, inconsistent capitals or extra decimals would make equal values look different.

## Read the output

The output keeps all four rows and both columns. The first name becomes `Ada Lovelace`, the second becomes `Lin Chen`, the missing name stays NULL and `Sam!` keeps its punctuation. The amounts don't change until you turn on Round Values.

Rounding to two decimal places produces `12.35`, `8.76` and `5.43`. The missing amount stays NULL. Removing null rows then leaves only the first two complete rows.

Cleanse changes values in place. It doesn't add separate cleaned columns. The null-row and null-column checks use the input values, so text that becomes an empty string after cleanup doesn't count as NULL.

To replace missing values rather than remove rows, use [Impute](https://oplema.com/docs/strata/prep/impute).

To extract text using a specific pattern, use [RegEx](https://oplema.com/docs/strata/parse/regex).

## Inputs and outputs

### Inputs

1 port

**In Required**

Accepts: Tables, Geospatial data.

Accepts one connection on this port.

### Outputs

1 port

**Out**

## Configure the block

Settings apply to columns of a matching type. A new block changes nothing until you turn on an operation.

**Trim Whitespace**

Remove leading and trailing spaces from text.

**Collapse Whitespace**

Replace runs of whitespace within text with a single space.

**Remove Letters**

Remove ASCII letters A-Z and a-z from text.

**Remove Digits**

Remove digits 0-9 from text. Numeric columns are unaffected.

**Remove Punctuation**

Remove characters outside ASCII letters, digits, underscores and whitespace. This can also remove accented letters and symbols.

**Modify Case**

Choose None, Upper, Lower or Title. Title capitalizes the first character of each space-separated word and lowercases the rest. Default: None.

**Round Values**

Enable rounding for decimal columns. Integer columns remain unchanged.

**Round Decimals**

Choose the number of decimal places when rounding is enabled. Use 0 for whole-number values; this doesn't cast the column to an integer type.

**Truncate To Date**

Convert timestamp columns to DATE, removing the time of day.

**Remove Null Rows**

Drop a row if any affected column contains NULL. The global setting applies to every column unless overridden.

**Remove Null Columns**

Drop affected columns whose input values are all NULL. A column with even one non-NULL value remains.

**Column Overrides**

Expand Per-Column Overrides and then a column to override an individual setting. Unset settings inherit the global options above; an explicit override takes precedence.

## Cleanse FAQs

**Does trimming turn empty text into NULL?**

No. Text with only spaces becomes an empty string. Remove Null Rows checks for NULL, so it keeps a row whose cleaned text is empty.

**Can I clean only one column?**

Yes. Leave the global operation off and turn it on in that column's overrides. You can also turn off a global operation for one column.

**In what order are text operations applied?**

Trim, collapse whitespace, remove letters, remove digits, remove punctuation, then change case. There is no second trim after character removal.

**Does Cleanse parse numbers stored as text?**

No. Rounding only applies to decimal number columns. Use Number first to turn formatted text into numbers.

### Prepare your data. Build it in Strata.

Strata opens in your browser, with no account.

[Open Strata](https://oplema.com/editor?path=%2F%3Fopen%3Dstrata) [About Strata](https://oplema.com/strata)
