Last tested: May 21, 2020
Are you trying to turn this - "Goku Power Level (957829)" into this - "Goku Power Level (957,829)"? You've come to the right place!
This can be accomplished in a Table Calculation by using a couple of string functions, namely the length and substring function. Let's examine the basic template we'll be following, in this case for a 6 digit number, and also assume that x = the name of the field you want to add commas to:
concat(substring(${x},0,length(${x})-3), ",", substring(${x}, length(${x})-2, length(${x})))
Why do we need so many functions?
Let's examine the code by working from the outside in of our template:
Let's use our example of 957829, and work from the inside out of our template:
Good catch! Since our formula only allows us elasticity within 4 to 6 digits, we would need a couple of conditional statement to handle numbers less than 4 digits and more than 6 digits. For numbers that are 1-9 digits long, we have a template here:
if(length(${x}) < 4, ${x},
if(length(${x}) > 6, concat(substring(${x}, 0, length(${x})-6), ",", substring(${x}, length(${x})-5, length(${x})-4), ",", substring(${x}, length(${x})-2, length(${x})))
,
concat(substring(${x},0,length(${x})-3), ",", substring(${x}, length(${x})-2, length(${x})))))
In this template, we first check to see if the length of x is less than 4 digits long, and if it is, we display the number as is. If that is false, we enter another conditional statement that checks to see if the length x is more than 6 digits long. If the length is more than 6 digits long, we use a slightly modified version of our earlier template (see if you can figure out how it works like we did before!), otherwise we would use our existing template since we know the numeric value is between 4 and 6 digits long.
This content is subject to limited support.