Add a CURRENCY() function for Formula Fields

Forum|Forum|7 years ago
December 20, 2018
29 replies
163 views

+27

Kamille_Parks11
Brainy

Right now, to format values pulled from a Currency field in a Formula field, users have to use something akin to the following formula to get “$65,756” to display as such. Otherwise, AirTable displays it as “65756”, no commas, no currency symbol:

IF(
  LEN(CONCATENATE({Trip Budget}))<4,
  {Trip Budget},
  IF(
AND(LEN(CONCATENATE({Trip Budget}))>3,LEN(CONCATENATE({Trip Budget}))<7),
LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
IF(
  AND(LEN(CONCATENATE({Trip Budget}))>6,LEN(CONCATENATE({Trip Budget}))<10),
  LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-6)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),6),3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
  IF(
    AND(LEN(CONCATENATE({Trip Budget}))>9,LEN(CONCATENATE({Trip Budget}))<13),
    LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-9)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),9),3)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),6),3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
  )
)
  )
)

This is obviously inconvenient, and it doesn’t even take into account decimals or amounts greater than $999,999,999.

I propose a new formula function that operates something like this:
CURRENCY([number or field reference], [currency symbol]) => CURRENCY(12345.6,"$") = $12,345.60

Show first post

Forum|pagination.label 2 / 2

Brandon_Gilbert
New Participant
Forum|Forum|4 years ago
November 22, 2021

I needed a similar solution that accounted for cents in currency. I’m using Zapier to connect Airtable data for custom proposals to my WordPress site that displays stylized/dynamic proposals. The data in JSON doesn’t retain the currency formatting, making my pricing tables a little hard to read. By default, a raw string won’t have “.00”, and if the string does have a tenth value but not a hundredth value (such as “0.50”), the output will not have an ending “0” (in the case of “.50”, the result will be 0.5").

These two issues with cents required a few nested solutions. I forked @Kamille_Parks initial formula and added my solutions for cents/decimals. Adding the decimals did require tweaking Kamille’s beautiful formula to account for the three extra characters, the decimal, and the two numbers after it. Here’s what I have, be sure to substitute/Find & Replace my {Subtotal} field for your own currency-based field that you want to format as a string):

IF(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<7,IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),
  IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>6,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<10),
LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6),
IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>9,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<13),
  LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-9)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),9),6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6),
  IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>12,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<16),
    LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-12)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),12),6)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),9),6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6)
  ))))```

Tristan11
New Participant
Forum|Forum|4 years ago
January 9, 2022

IF(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<7,IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),
  IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>6,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<10),
LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6),
IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>9,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<13),
  LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-9)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),9),6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6),
  IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>12,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<16),
    LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-12)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),12),6)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),9),6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6)
  ))))```

This is great, but I find it blanks when there’s a 0 in the hundreds place. Not sure if it also blanks when 0s are in other places. E.g., referencing $176,011.66, the formula produces a blank.

Keri_Caraher
New Participant
Forum|Forum|3 years ago
March 6, 2022

IF(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<7,IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),
  IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>6,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<10),
LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6),
IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>9,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<13),
  LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-9)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),9),6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6),
  IF(AND(LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))>12,LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))<16),
    LEFT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),LEN(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))))-12)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),12),6)&","&LEFT(RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),9),6)&","&RIGHT(CONCATENATE(IF(SUM(FIND(".",CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))+2)!=LEN(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})))),CONCATENATE(CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))),"0"),CONCATENATE(IF(IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal})=".00","0.00",IF(SEARCH(".",CONCATENATE({Subtotal}))="",CONCATENATE({Subtotal},".00"),{Subtotal}))))),6)
  ))))```

THANK YOU BRANDON!!! :grinning: :100: :index_pointing_at_the_viewer:

+13

Jeri_Vespoli
Participating Frequently
Forum|Forum|3 years ago
March 15, 2022

Adding my vote to a Currency() and/or Number({numeric field}, format) function!
Using a numeric field in a formula results in a string, dropping the zeros, and adding +0 does not convert it back to a number. The LEN function is way too much logic for something so simple. @AlliAlosa

This: CONCATENATE(IF({Salary To}>’’,‘Salary to: $’&{Salary To},’’),’ ‘,IF(AND({Salary Type}=‘Hour’,{Salary To}>’’),‘HR’,IF(AND({Salary Type}=‘Year’,{Salary To}>’’),‘YR’,’’)))

should be able to be easily formatted to produce this: Salary to: $23.50 HR not this: Salary to: $23.5 HR

Forum|pagination.label 2 / 2

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded