Treat an accounting dash as zero

When your source says a dash means zero, read - as 0. Blank stays blank.

What you'll make

In Excel: A dash - becomes 0, a blank stays blank.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TRIM(A8)=""Checks if the cell is empty.FALSE
2NUMBERVALUE(A8,".",",")Reads the text as a number. A period marks decimals.Not used for row 8
3IF(TRIM(A8)="-",0, … )Gives 0 for a lone dash, otherwise uses part 2.0
4IF( … ,"", … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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