---
title: "Clean up inconsistent text in Strata | Oplema"
description: "Learn to trim stray spaces and fix case with Cleanse in Strata so each status and bike code has one spelling, then count the spellings before and after."
url: "https://oplema.com/learn/export-to-report/clean-up-text"
updated: "2026-11-04"
---

Lesson 4 of 10 · Lab

# Clean up inconsistent text in a CSV

Cleanse fixes how text is written without changing what it means. It trims stray spaces and makes case consistent, either across every column or for one column at a time.

To a computer, `completed`, `Completed` and `completed` with a trailing space are three values. A count by status splits one group three ways, and a filter on one spelling misses the others.

At [fictional Kramerica Bikes](https://oplema.com/learn/export-to-report/first-data-flow), Harbour's staff can edit a booking at the till, and some retype the status or bike code as they go. This live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep, starts with the six columns you kept in the last lesson.

Cleanse can make every text column lower case at once. Why not do that here?

`booking_id` would turn from KB-10001 into kb-10001 and stop matching other files, and bike codes are written in capitals. Each column needs its own rule.

## One spelling per status and bike code

1. **Summarize** opens first. `statuses` counts 12 spellings of what should be three statuses, and `codes` counts 15 for four bike codes.
2. Select **Cleanse** and select **Trim Whitespace**.
3. Select **Summarize**. Spellings that differed only by a space have merged: `statuses` and `codes` both read 8.
4. Select **Cleanse**. Open **Per-Column Overrides**, select the arrow at the right of `status`, scroll down and set its **Modify Case** to **Lower**.
5. Do the same for `bike_code`, with **Modify Case** set to **Upper**.
6. Select **Summarize**. `statuses` reads 3, `codes` reads 4 and `rows` is still 321.

What you should see

Three statuses: 286 `completed`, 29 `cancelled` and 6 `no-show`. Four codes: CTY, EBK, KID and TDM. Still 321 rows, because cleaning changes values, never how many rows there are.

## Cleaning keeps every row

Trim applies to every text column, because a stray space never means anything. Case applies only to the columns you name, because in an ID it can matter. Both rules sit in one block, where anyone reading the flow can see them.

Cleanse can't tell you what a value should be. It merged `Completed` into `completed` only because you said case doesn't matter in `status`. Next, the amounts and dates, which are still text.

See the [Cleanse reference](https://oplema.com/docs/strata/prep/cleanse). For later: [Null vs blank vs zero](https://oplema.com/learn/data-preparation/cleanse-and-nulls) covers missing values, the other half of cleaning.

## Common text-cleaning mistakes

- **Lowercasing every column.** IDs and codes can depend on case, so set case per column.
- **Fixing spellings by hand in a spreadsheet.** Next week's file arrives with the same mess, and a hand fix doesn't come with it. A block does.
- **Checking by eye.** A trailing space is invisible in a preview. Count the distinct values, as Summarize does here.

## TRIM and LOWER in SQL and Excel

```
SELECT  count ( DISTINCT  LOWER ( TRIM ( status )))  AS  statuses,
       count ( DISTINCT  UPPER ( TRIM (bike_code)))  AS  codes
FROM  harbour;
```

SQL cleans inside the query, so every query has to repeat it. In Excel, `=LOWER(TRIM(A2))` in a helper column does the same, and `=UNIQUE()` on the result lists the spellings that are left.
