Inserting Multiple Separators into a String Containing only Numbers

Published: 21 Oct 2016
Last Modified Date: 20 Jul 2023

Question

How to format a string of numbers to display thousand separators in Tableau Desktop.

Environment

Tableau Desktop

Answer

IF numbers are converted to strings, comma separator will be missing.
For example there is a string fields [SUM_Sales(Str)] converted from number via the calculation below
STR(INT(SUM([Sales])))

To insert separators into the strings
Method 1 - For data source that supports REGEXP_REPLACE
Use the following regular expression in a calculated field:

REGEXP_REPLACE([Num],'(\d{1,3}|\G\d{3})(?=(?:\d{3})+(?!\d))','$1.')

Note: Such a workaround will only work for data sources which allow regex functions.

Method 2 - For data source that does not support REGEXP_REPLACE
Create a calculation to process with every 3 digits from right side.
Added more ELSEIF branches to the calculation below if the length of the string is more than 9 digits.
IF LEN([SUM_Sales(Str)])<=3 THEN
[SUM_Sales(Str)]
ELSEIF LEN([SUM_Sales(Str)])>3 AND LEN([SUM_Sales(Str)])<=6 THEN
LEFT([SUM_Sales(Str)],LEN([SUM_Sales(Str)])-3)+","+RIGHT([SUM_Sales(Str)],3)

ELSEIF LEN([SUM_Sales(Str)])>6 AND LEN([SUM_Sales(Str)])<=9 THEN
LEFT([SUM_Sales(Str)],LEN([SUM_Sales(Str)])-6)+","+MID([SUM_Sales(Str)],LEN([SUM_Sales(Str)])-5,3)+","+RIGHT([SUM_Sales(Str)],3)
ELSE
"more than 9 digits!"
END

***Please refer to the sample_.twbx for more details