Show a count, money or text allowance

Turn Money 1500 USD into USD 1500.00, and keep Unlimited as text, not 0.

What you'll make

In Excel: Money, 1500, USD becomes USD 1500.00.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXT(B8,"0")Writes a count as a whole number.Not used for row 8
2C8&" "&TEXT(B8,"0.00")Currency code, a space, the amount with 2 decimals."USD 1500.00"
3B8&""Keeps words like Unlimited exactly as typed.Not used for row 8
4SWITCH( … )Picks part 1, 2 or 3 from the value type."USD 1500.00"

unsignedInt uses part 1, Money part 2, string part 3. Any other type shows Review benefit value type.

=SWITCH(A8,"unsignedInt",TEXT(B8,"0"),"Money",C8&" "&TEXT(B8,"0.00"),"string",B8&"","Review benefit value type")

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 allowances. Paste each value type, its value, and the currency for Money rows into the green columns of the Template tab. The allowances fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Type each value type exactly as unsignedInt, Money or string. The values here are made-up.

Microsoft's help: SWITCH function