Salesforceカスタム項目の数式で、数字を通貨フォーマット(カンマ区切り)で表示するには

イシュー

参考

REGEX関数使えない

  • 「関数REGEXは、この種類の数式では使用できません」でエラー
  • REGEXが使えるのは入力規則のみ

結局

  • 大学時代以来ぐらいに頭の悪そうなコード書いた
  CASE( LEN(TEXT(ROUND(Price__c,0))),
    1, MID(TEXT(Price__c),1,1),
    2, MID(TEXT(Price__c),1,2),
    3, MID(TEXT(Price__c),1,3),
    4, MID(TEXT(Price__c), 1, 1) & "," & MID(TEXT(Price__c),2,3),
    5, MID(TEXT(Price__c), 1, 2) & "," & MID(TEXT(Price__c),3,3),
    6, MID(TEXT(Price__c), 1, 3) & "," & MID(TEXT(Price__c),4,3),
    7, MID(TEXT(Price__c), 1, 1) & "," & MID(TEXT(Price__c), 2, 3) & "," & MID(TEXT(Price__c),5,3),
    8, MID(TEXT(Price__c), 1, 2) & "," & MID(TEXT(Price__c), 3, 3) & "," & MID(TEXT(Price__c),6,3),
    9, MID(TEXT(Price__c), 1, 3) & "," & MID(TEXT(Price__c), 4, 3) & "," & MID(TEXT(Price__c),7,3),
    "ng"
  )

ちょっと最適化した

  MID(TEXT(Price__c),1,LEN(TEXT(FLOOR(Price__c)))-(FLOOR((LEN(TEXT(FLOOR(Price__c)))-1)/3)*3)) &
  IF(LEN(TEXT(FLOOR(Price__c)))>3,
    "," & MID(TEXT(Price__c),LEN(TEXT(FLOOR(Price__c)))-(FLOOR((LEN(TEXT(FLOOR(Price__c)))-1)/3)*3-1),3),
    ""
  ) &
  IF(LEN(TEXT(FLOOR(Price__c)))>6,
    "," & MID(TEXT(Price__c),LEN(TEXT(FLOOR(Price__c)))-(FLOOR((LEN(TEXT(FLOOR(Price__c)))-1)/3)*3-4),3),
    ""
  )