How to add commas to a Numeric String value in a Table Calculation

[iqbal_sandhu]

Knowledge Drop

Last tested: May 21, 2020
 

The Problem

Are you trying to turn this - "Goku Power Level (957829)" into this - "Goku Power Level (957,829)"? You've come to the right place!

A Solution

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:

1. concat - From a high level, all we're doing is we're concatenating the numbers before the comma, to the ones after, i.e. concat(numbers before comma, ",", numbers after comma).
2. substring - We're using substring to extract the numbers we want, and the definition shows the basic form. For the first substring function, we're going from the first digit to the comma, and for the second we're going from the comma to the last digit.
3. length - The reason we use length instead of an actual digit is because we want to be flexible. We want this table calc to handle numbers like 999,999 and also 1,234. To do that, we use length to count the amount of digits and use that in the substring function to dynamically calculate the position the comma will be inserted to.

Why does this work?

Let's use our example of 957829, and work from the inside out of our template:

1. length(x) - We use this chunk three times, once without any additional math, and twice with some subtraction. We use subtraction, because this allows us a certain amount of elasticity between 4 and 6 digits. If we plug in our example number of 957829, we find that length(957829) - 3 = 3 and that length(957829) -2 = 4. These are the digit numbers which we would insert the comma in between.
2. substring(x, 0, length(x)-3) - Now that we know how long our value is, we can substring it and extract the first three digits! We do this by telling the function to start at 0 and go until the first number before the comma, in this case the 3rd digit. Using our example and plugging it into this formula, we would get 957. Later, we run substring(x, length(x)-2, length(x)) and using our example we'd get 829.
3. concat - Continuing out, we're now left with concat(957, ",", 829), which leaves us with our final result of 957,829.

What about other numbers bigger than 6 digits or less than 4?

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.