Export a signed nine-digit cent amount
Turn 123.45 into +000012345. Amounts too large for nine digits are flagged.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | n,ROUND(A8*100,0) | Turns the amount into whole cents, called n. | 12345 |
| 2 | ABS(n)>999999999,"Review width" | Flags amounts too large for nine digits. | Not used for row 8 |
| 3 | IF(n<0,"-","+") | Gives - for a negative amount, otherwise +. | "+" |
| 4 | TEXT(ABS(n),"000000000") | Writes the cents as nine digits, with leading zeros. | "000012345" |
| 5 | LET( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own amounts. Paste amounts into the green column of the Template tab. The cent fields 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 the file you send to asks for a sign plus nine cent digits.
Microsoft's help: LET function



