Read space-grouped amounts
Turn 1 234,56 EUR into 1234.56, even with nonbreaking or narrow spaces.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | SUBSTITUTE(A8,B8,"") | Takes the currency label out of the amount. | "1 234,56 " |
| 2 | SUBSTITUTE( … ,UNICHAR(160)," ") | Turns nonbreaking spaces into plain spaces. | "1 234,56 " |
| 3 | SUBSTITUTE( … ,UNICHAR(8239)," ") | Turns narrow spaces into plain spaces. | "1 234,56 " |
| 4 | NUMBERVALUE( … ,","," ") | Reads it as a number: comma for decimals, spaces for thousands. | 1234.56 |
Each bracket wraps the one above it, so Excel works from the inside out. Nonbreaking and narrow spaces look like plain ones.
=NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,B8,""),UNICHAR(160)," "),UNICHAR(8239)," "),","," ")How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own list. Paste amount text into the first green column and its currency label next to it. The amounts fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only for amounts with a comma for decimals. It doesn't convert between currencies.
Microsoft's help: NUMBERVALUE function



