# Clean imported text

Paste any recipe into an empty cell.

FormulaSmith | 2026-10-08 | EN

English Excel: comma arguments. Regional settings may use another separator.

The formulas contain their own example inputs. Paste one into an empty cell. Text in quotes is literal data.

## Extra ordinary spaces

```excel
=TRIM("  Ada   Lovelace  ")
```

RESULT: Ada Lovelace

Removes edge spaces and keeps one space between words.

## Remove a known separator

```excel
=SUBSTITUTE("AB-001","-","")
```

RESULT: AB001

All hyphens are removed. Apply only where the data rules allow it.

## A nonbreaking space

```excel
=TRIM(SUBSTITUTE("Ada"&UNICHAR(160)&"Lovelace",UNICHAR(160)," "))
```

RESULT: Ada Lovelace

Replace Unicode 160 with a normal space before trimming.

## Keep the prefix

```excel
=LEFT("AB-001",2)
```

RESULT: AB

Takes exactly two characters, not everything before a separator.

## Keep leading zeros

```excel
=RIGHT("AB-001",3)
```

RESULT: 001 (text)

Do not convert this result to a number when it is an identifier.

## Parse a known number format

```excel
=NUMBERVALUE("1.234,50",",",".")
```

RESULT: 1234.5

Arguments specify decimal comma and grouping dot explicitly.

## BEFORE YOU USE IT

Keep the raw data. Preserve leading zeros in IDs. TRIM removes ordinary spaces, not every Unicode space. NUMBERVALUE returns a number, not formatted currency.

## MICROSOFT REFERENCES

- [TRIM](https://support.microsoft.com/en-us/excel/functions/trim-function) (2026-10-08)
- [NUMBERVALUE](https://support.microsoft.com/en-us/excel/functions/numbervalue-function) (2026-10-08)

https://formulasmith.com/en/cheat-sheets/