Treat an accounting dash as zero
When your source says a dash means zero, read - as 0. Blank stays blank.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TRIM(A8)="" | Checks if the cell is empty. | FALSE |
| 2 | NUMBERVALUE(A8,".",",") | Reads the text as a number. A period marks decimals. | Not used for row 8 |
| 3 | IF(TRIM(A8)="-",0, … ) | Gives 0 for a lone dash, otherwise uses part 2. | 0 |
| 4 | IF( … ,"", … ) | Leaves the result blank when the cell is empty. | 0 |
Row 8 is a lone dash, so it gives 0 and part 2 isn't needed. The amounts are made-up.
=IF(TRIM(A8)="","",IF(TRIM(A8)="-",0,NUMBERVALUE(A8,".",",")))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 amounts. Paste amounts like 1,234.50 or - into the green column of the Template tab. The numbers 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 when your source says a dash means zero. In other files a dash can mean missing.
Microsoft's help: IF function
OTHER ROUTES YOU MIGHT LIKE

Convert a math minus sign in amounts
−1,234.50 with a math minus → -1234.5
Get the template →
Put the decimal point back in amounts
12345 with scale 2 → 123.45
Get the template →
Read text amounts in a mixed column
1,234.56 as text → 1234.56; numbers stay as they are
Get the template →
Show $1,234.50 instead of 1234.5 in a mail merge
1234.5 → $1,234.50
Take this route →