Read amounts like 90.02- as negatives

Turn 90.02- from an accounting export into the number -90.02.

What you'll make

In Excel: 90.02- becomes -90.02.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1IF(RIGHT( … )="-",-1,1)Gives -1 if the amount ends in a minus, else 1.-1
2SUBSTITUTE( … ,"-","")Takes the minus sign out of the amount."90.02"
3NUMBERVALUE( … ,".",",")Reads it as a number. A period marks decimals.90.02
4IF( … )*NUMBERVALUE( … )Multiplies the number by part 1, so the minus comes back.-90.02

Part 3 wraps part 2, so Excel works from the inside out. The amounts here are made-up.

=IF(RIGHT(TRIM(A8),1)="-",-1,1)*NUMBERVALUE(SUBSTITUTE(TRIM(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 amount text 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 a minus at the end means negative. Amounts like 1.234,56 won't read right.

Microsoft's help: NUMBERVALUE function