More than 1 year has passed since last update.

3桁ごとにカンマをつける

Salesforce

Last updated at 2022-11-02Posted at 2021-06-08

以前も作ったけど、こちらは少々進化版

CASE ( LEN(text ( X20220303ca__c )),
4, LEFT(text ( X20220303ca__c ),1) + "," + RIGHT(text ( X20220303ca__c ),3),
5, LEFT(text ( X20220303ca__c ),2) + "," + RIGHT(text ( X20220303ca__c ),3),
6, LEFT(text ( X20220303ca__c ),3) + "," + RIGHT(text ( X20220303ca__c ),3),

7, LEFT(text ( X20220303ca__c ),1) + "," + MID(text ( X20220303ca__c ),2,3) + "," + RIGHT(text ( X20220303ca__c ),3),
8, LEFT(text ( X20220303ca__c ),2) + "," + MID(text ( X20220303ca__c ),3,3) + "," + RIGHT(text ( X20220303ca__c ),3),
9, LEFT(text ( X20220303ca__c ),3) + "," + MID(text ( X20220303ca__c ),4,3) + "," + RIGHT(text ( X20220303ca__c ),3),

10, LEFT(text ( X20220303ca__c ),1) + "," + MID(text ( X20220303ca__c ),2,3) + "," + MID(text ( X20220303ca__c ),5,3) + "," + RIGHT(text ( X20220303ca__c ),3),
11, LEFT(text ( X20220303ca__c ),2) + "," + MID(text ( X20220303ca__c ),3,3) + "," + MID(text ( X20220303ca__c ),6,3) + "," + RIGHT(text ( X20220303ca__c ),3),
12, LEFT(text ( X20220303ca__c ),3) + "," + MID(text ( X20220303ca__c ),4,3) + "," + MID(text ( X20220303ca__c ),7,3) + "," + RIGHT(text ( X20220303ca__c ),3),

text ( X20220303ca__c ))

エクスポートしたレポートのJPY表記を消し、カンマを増やしたい

初期バージョン

数値をテキスト型に変換してしまったので、カンマが付かなくなったようです。

currency prefix in a formula field

IF( Annual_Contract_Value__c * Budget_Rate__r.Rate__c < 1000, TEXT(Annual_Contract_Value__c * Budget_Rate__r.Rate__c),

IF(Annual_Contract_Value__c * Budget_Rate__r.Rate__c < 1000000, TEXT(FLOOR(Annual_Contract_Value__c * Budget_Rate__r.Rate__c /1000))
& ','
& RIGHT(TEXT(Annual_Contract_Value__c * Budget_Rate__r.Rate__c), LEN(TEXT(Annual_Contract_Value__c * Budget_Rate__r.Rate__c)) - LEN(TEXT(FLOOR(Annual_Contract_Value__c * Budget_Rate__r.Rate__c /1000)))),

IF(Annual_Contract_Value__c * Budget_Rate__r.Rate__c < 1000000000, TEXT(FLOOR(Annual_Contract_Value__c * Budget_Rate__r.Rate__c /1000000))
& ','
& MID(TEXT(Annual_Contract_Value__c * Budget_Rate__r.Rate__c), 1 + LEN(TEXT(FLOOR(Annual_Contract_Value__c * Budget_Rate__r.Rate__c /1000000))),3)
& ','
& RIGHT(TEXT(Annual_Contract_Value__c * Budget_Rate__r.Rate__c), LEN(TEXT(Annual_Contract_Value__c * Budget_Rate__r.Rate__c)) - LEN(TEXT(FLOOR(Annual_Contract_Value__c * Budget_Rate__r.Rate__c /1000)))),

IF(Annual_Contract_Value__c * Budget_Rate__r.Rate__c >= 1000000000, "#TooHigh!", NULL))))

まぁ、ぐぐれば同じような質問があるのでそれ自体は難しくない。
ただ、最初に質問の意味が理解できなかったです。
英語はわからん。

小数点の桁数を固定で２つにしたいという複合バージョン

IF(CONTAINS(TEXT(Total_Amount__c),"."),
  IF (LEFT(RIGHT(TEXT(Total_Amount__c),3),1) =".",
        CASE ( LEN(text ( Total_Amount__c )),
            7, LEFT(text ( Total_Amount__c ),1) + "," + MID(text ( Total_Amount__c ),2,3) + RIGHT(text ( Total_Amount__c ),3),
            8, LEFT(text ( Total_Amount__c ),2) + "," + MID(text ( Total_Amount__c ),3,3) + RIGHT(text ( Total_Amount__c ),3),
            9, LEFT(text ( Total_Amount__c ),3) + "," + MID(text ( Total_Amount__c ),4,3) + RIGHT(text ( Total_Amount__c ),3),

            10, LEFT(text ( Total_Amount__c ),1) + "," + MID(text ( Total_Amount__c ),2,3) + "," + MID(text ( Total_Amount__c ),5,3) + RIGHT(text ( Total_Amount__c ),3),
            11, LEFT(text ( Total_Amount__c ),2) + "," + MID(text ( Total_Amount__c ),3,3) + "," + MID(text ( Total_Amount__c ),6,3) + RIGHT(text ( Total_Amount__c ),3),
            12, LEFT(text ( Total_Amount__c ),3) + "," + MID(text ( Total_Amount__c ),4,3) + "," + MID(text ( Total_Amount__c ),7,3) + RIGHT(text ( Total_Amount__c ),3),

        text ( Total_Amount__c )),

        CASE ( LEN(text ( Total_Amount__c )),
        6, LEFT(text ( Total_Amount__c ),3) + "," + RIGHT(text ( Total_Amount__c ),3),

        7, LEFT(text ( Total_Amount__c ),1) + "," + MID(text ( Total_Amount__c ),2,3) + "," + RIGHT(text ( Total_Amount__c ),3),
        8, LEFT(text ( Total_Amount__c ),2) + "," + MID(text ( Total_Amount__c ),3,3) + "," + RIGHT(text ( Total_Amount__c ),3),
        9, LEFT(text ( Total_Amount__c ),3) + "," + MID(text ( Total_Amount__c ),4,3) + "," + RIGHT(text ( Total_Amount__c ),3),

        10, LEFT(text ( Total_Amount__c ),1) + "," + MID(text ( Total_Amount__c ),2,3) + "," + MID(text ( Total_Amount__c ),5,3) + "," + RIGHT(text ( Total_Amount__c ),3),
        11, LEFT(text ( Total_Amount__c ),2) + "," + MID(text ( Total_Amount__c ),3,3) + "," + MID(text ( Total_Amount__c ),6,3) + "," + RIGHT(text ( Total_Amount__c ),3),
        12, LEFT(text ( Total_Amount__c ),3) + "," + MID(text ( Total_Amount__c ),4,3) + "," + MID(text ( Total_Amount__c ),7,3) + "," + RIGHT(text ( Total_Amount__c ),3),

        text ( Total_Amount__c )) + "0"
),
      
    CASE ( LEN(text ( Total_Amount__c )),
        4, LEFT(text ( Total_Amount__c ),1) + "," + RIGHT(text ( Total_Amount__c ),3),
        5, LEFT(text ( Total_Amount__c ),2) + "," + RIGHT(text ( Total_Amount__c ),3),
        6, LEFT(text ( Total_Amount__c ),3) + "," + RIGHT(text ( Total_Amount__c ),3),

        7, LEFT(text ( Total_Amount__c ),1) + "," + MID(text ( Total_Amount__c ),2,3) + "," + RIGHT(text ( Total_Amount__c ),3),
        8, LEFT(text ( Total_Amount__c ),2) + "," + MID(text ( Total_Amount__c ),3,3) + "," + RIGHT(text ( Total_Amount__c ),3),
        9, LEFT(text ( Total_Amount__c ),3) + "," + MID(text ( Total_Amount__c ),4,3) + "," + RIGHT(text ( Total_Amount__c ),3),

        10, LEFT(text ( Total_Amount__c ),1) + "," + MID(text ( Total_Amount__c ),2,3) + "," + MID(text ( Total_Amount__c ),5,3) + "," + RIGHT(text ( Total_Amount__c ),3),
        11, LEFT(text ( Total_Amount__c ),2) + "," + MID(text ( Total_Amount__c ),3,3) + "," + MID(text ( Total_Amount__c ),6,3) + "," + RIGHT(text ( Total_Amount__c ),3),
        12, LEFT(text ( Total_Amount__c ),3) + "," + MID(text ( Total_Amount__c ),4,3) + "," + MID(text ( Total_Amount__c ),7,3) + "," + RIGHT(text ( Total_Amount__c ),3),

        text ( Total_Amount__c )
        ) + ".00"
    )

Help with formula

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up