Salesforceの数式で数値をカンマ付きテキストへ変換することがあったため、汎用的に使えるものをまとめておきます。
はじめに
Salesforceの数式で数値をテキストへ変換すると、通常はカンマなしの数値へ変換されます。
しかし、テキストへ数値を結合したい等の場合、カンマ付きのテキストへ変換された方が嬉しいかと思います。
このような場合に対応できるようなテンプレートの数式を考えてみようと思います。
最終的な数式
先に出来上がった数式を示します。
IF( 数値 < 0 , "-" , "" )
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) * ( 10 ^ 18 ) ) / ( 10 ^ 15 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) * ( 10 ^ 18 ) ) / ( 10 ^ 15 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) * ( 10 ^ 15 ) ) / ( 10 ^ 12 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) * ( 10 ^ 15 ) ) / ( 10 ^ 12 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) * ( 10 ^ 12 ) ) / ( 10 ^ 9 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) * ( 10 ^ 12 ) ) / ( 10 ^ 9 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) * ( 10 ^ 9 ) ) / ( 10 ^ 6 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) * ( 10 ^ 9 ) ) / ( 10 ^ 6 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) * ( 10 ^ 6 ) ) / ( 10 ^ 3 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) * ( 10 ^ 6 ) ) / ( 10 ^ 3 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) * ( 10 ^ 3 ) ) / ( 10 ^ 0 ) ) ) , 3 , "0" ) ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) * ( 10 ^ 3 ) ) / ( 10 ^ 0 ) ) )
)
+ "." + LPAD( TEXT( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) ) ) * ( 10 ^ [小数の桁数] ) ) , [小数の桁数] , "0" )
ここで、パラメータは下記で設定します。
- 小数の桁数はテキストへ変換したい数値の小数点以下の桁数の数値
考え方のプロセス
考え方のプロセスとしては、下記の順序になります。
- 符号をテキストで取り出す
- 整数部と小数部を分割する
- 整数部の特定の桁数の範囲の値を抜き出す
- 整数部をテキストへ変換する
- 整数部のテキストを結合する
- 小数部をテキストへ変換する
- テキストを結合する
符号をテキストで取り出す
符号をテキストで取り出す処理は下記のように行います。
IF( 数値 < 0 , "-" , "" )
整数部と小数部を分割する
整数部と小数部は処理が異なるため、整数部と小数部を分割する方法を考えます。
整数部は簡単に下記のように取り出すことが可能です。
FLOOR( ABS( 数値) )
小数部に関しては、整数部と項目で定義される小数の桁数に依存することを意識する必要があります。
( ABS( 数値 ) - FLOOR( ABS( 数値 ) ) ) * ( 10 ^ [小数の桁数] )
整数部の特定の桁数の範囲の値を抜き出す
整数部の特定の桁数の範囲を抜き出すには、下記を考える必要があります。
- 特定の桁数の範囲の最小の桁数より小さい桁数の値を除去する
- 特定の桁数の範囲の最大の桁数より大きい桁数の値を除去する
まず、特定の桁数の範囲の最小の桁数より小さい桁数の値を除去するためには下記のように行います。
FLOOR( ABS( 数値 ) / ( 10 ^ ( [特定の桁数の範囲の最小の桁数] - 1 ) ) )
次に、特定の桁数の範囲の最大の桁数より大きい桁数の値を除去するためには下記のように行います。
ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ [特定の桁数の範囲の最大の桁数] ) ) * ( 10 ^ [特定の桁数の範囲の最大の桁数] )
これらをまとめると下記のような形になります。
FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ [特定の桁数の範囲の最大の桁数] ) ) * ( 10 ^ [特定の桁数の範囲の最大の桁数] ) ) / ( 10 ^ ( [特定の桁数の範囲の最小の桁数] - 1 ) ) )
整数部をテキストへ変換する
整数部をテキストへ変換する際には、3桁毎にカンマ付きの結合を行う必要があります。
また、項目で定義される整数部の桁数についても意識する必要がありますが、Salesforceの項目で定義される整数部の桁数の最大値は18桁となるため、18桁より大きな値に関しては考慮する必要がないものとします。
そのため、整数部の桁数に関しては、
- 18桁 ~ 16桁
- 15桁 ~ 13桁
- 12桁 ~ 10桁
- 9桁 ~ 7桁
- 6桁 ~ 4桁
- 3桁 ~ 1桁
というような形で分解すると良いことがわかります。
そのため、それぞれの桁数のグループをテキストへ変換するには、下記のような形にすることができます。
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) * ( 10 ^ 18 ) ) / ( 10 ^ 15 ) ) )
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) * ( 10 ^ 15 ) ) / ( 10 ^ 12 ) ) )
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) * ( 10 ^ 12 ) ) / ( 10 ^ 9 ) ) )
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) * ( 10 ^ 9 ) ) / ( 10 ^ 6 ) ) )
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) * ( 10 ^ 6 ) ) / ( 10 ^ 3 ) ) )
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) * ( 10 ^ 3 ) ) / ( 10 ^ 0 ) ) )
整数部のテキストを結合する
結合する際に注意すべき点として、結合する桁数の値が存在するかどうかを確認する必要があります。
また、結合する桁数の最大の桁数よりも大きい桁数の値が存在する場合に
- カンマを付ける
- 3桁の0埋めをする
という処理を挟む必要があります。
この処理は下記のように行うことができます。
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ [特定の桁数の範囲の最大の桁数] ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ [特定の桁数の範囲の最大の桁数] ) ) * ( 10 ^ [特定の桁数の範囲の最大の桁数] ) ) / ( 10 ^ ( [特定の桁数の範囲の最小の桁数] - 1 ) ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ ( [特定の桁数の範囲の最小の桁数] - 1 ) ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ [特定の桁数の範囲の最大の桁数] ) ) * ( 10 ^ [特定の桁数の範囲の最大の桁数] ) ) / ( 10 ^ ( [特定の桁数の範囲の最小の桁数] - 1 ) ) ) ) ,
""
)
)
これを考慮して、整数部のテキストを結合すると、下記のような数式が完成します。
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) * ( 10 ^ 18 ) ) / ( 10 ^ 15 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) * ( 10 ^ 18 ) ) / ( 10 ^ 15 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) * ( 10 ^ 15 ) ) / ( 10 ^ 12 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) * ( 10 ^ 15 ) ) / ( 10 ^ 12 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) * ( 10 ^ 12 ) ) / ( 10 ^ 9 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) * ( 10 ^ 12 ) ) / ( 10 ^ 9 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) * ( 10 ^ 9 ) ) / ( 10 ^ 6 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) * ( 10 ^ 9 ) ) / ( 10 ^ 6 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) * ( 10 ^ 6 ) ) / ( 10 ^ 3 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) * ( 10 ^ 6 ) ) / ( 10 ^ 3 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) * ( 10 ^ 3 ) ) / ( 10 ^ 0 ) ) ) , 3 , "0" ) ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) * ( 10 ^ 3 ) ) / ( 10 ^ 0 ) ) )
)
整数部の桁数によっては不要となる部分がある点に注意が必要です
小数部をテキストへ変換する
小数部をテキストへ変換することは簡単で、下記のように行います。
LPAD( TEXT( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) ) ) * ( 10 ^ [小数の桁数] ) ) , [小数の桁数] , "0" )
小数部の桁数が0の場合は不要です
テキストを結合する
出来上がった整数部の数式と小数部の数式を結合して完成です。
IF( 数値 < 0 , "-" , "" )
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) * ( 10 ^ 18 ) ) / ( 10 ^ 15 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 18 ) ) * ( 10 ^ 18 ) ) / ( 10 ^ 15 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) * ( 10 ^ 15 ) ) / ( 10 ^ 12 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 15 ) ) * ( 10 ^ 15 ) ) / ( 10 ^ 12 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) * ( 10 ^ 12 ) ) / ( 10 ^ 9 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 12 ) ) * ( 10 ^ 12 ) ) / ( 10 ^ 9 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) * ( 10 ^ 9 ) ) / ( 10 ^ 6 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 9 ) ) * ( 10 ^ 9 ) ) / ( 10 ^ 6 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) * ( 10 ^ 6 ) ) / ( 10 ^ 3 ) ) ) , 3 , "0" ) ,
IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) > 0 ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 6 ) ) * ( 10 ^ 6 ) ) / ( 10 ^ 3 ) ) ) ,
""
)
)
+ IF(
FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) > 0 ,
"," + LPAD( TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) * ( 10 ^ 3 ) ) / ( 10 ^ 0 ) ) ) , 3 , "0" ) ,
TEXT( FLOOR( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) / ( 10 ^ 3 ) ) * ( 10 ^ 3 ) ) / ( 10 ^ 0 ) ) )
)
+ "." + LPAD( TEXT( ( ABS( 数値 ) - FLOOR( ABS( 数値 ) ) ) * ( 10 ^ [小数の桁数] ) ) , [小数の桁数] , "0" )
まとめ
フォーマット機能があれば簡単にできるものですが、フォーマット機能がない場合に如何に処理を考えられるかがキモですね。