Export a signed nine-digit cent amount

Turn 123.45 into +000012345. Amounts too large for nine digits are flagged.

What you'll make

In Excel: 123.45 becomes +000012345.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1n,ROUND(A8*100,0)Turns the amount into whole cents, called n.12345
2ABS(n)>999999999,"Review width"Flags amounts too large for nine digits.Not used for row 8
3IF(n<0,"-","+")Gives - for a negative amount, otherwise +."+"
4TEXT(ABS(n),"000000000")Writes the cents as nine digits, with leading zeros."000012345"
5LET( … )Joins the sign and the nine digits."+000012345"

A tiny negative that rounds to zero gets +. The amounts are made-up.

=LET(n,ROUND(A8*100,0),IF(ABS(n)>999999999,"Review width",IF(n<0,"-","+")&TEXT(ABS(n),"000000000")))

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 into the green column of the Template tab. The cent fields 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 the file you send to asks for a sign plus nine cent digits.

Microsoft's help: LET function