How to convert strings in Tableau Desktop, like we use “=Text()” function in MS Excel?

1
votes

I am using Tableau Desktop 10.x, and have a LIVE connection from MS Excel file.

I have a column with some numbers as below:

123
1234
12345

However, I want to change or create another column to see the results as below:

ID000123
ID001234
ID012345

I know there is a function in Excel to covert strings in a specific format --> "=Text()" , for example below excel formula will convert the string in a specific format:

=Text(A1,"ID000000")

Please help me as I want this trick/results in the Tableau data.

2
tableau-api
as you have picked the excel-formula tag, why not just create that info in excel and pull it into where you need it. – Solar Mike
You can add the 'ID' prefix in the format field of your number, but you can't (to my knowledge) achieve the fixed 0's that your looking for in Tableau. Since your connection is live, then @SolarMike suggestions to simply place this in the data source seems the most elegant. – Ben P

2 Answers

1
votes

Bernardo's answer is the one I'm most familiar with. Recently, I came across another way to do it without if statements. Say you need something like ID000000003948, you can consolidate Bernardo's formula into this dynamic one that doesn't rely on if statements.

"ID" + REPLACE(SPACE(12-LEN(Str([Sales]))), " ", "0") + Str([Sales])

where the number 12 in the formula is the total length, including zeroes, that the padded string needs to be.

2
votes

This should work. Assume [Sales] is your number measure. 

if len(str([Sales])) = 3 then "ID000"+str([Sales]) 
elseif len(str([Sales])) = 4 then "ID00"+str([Sales])
elseif len(str([Sales])) = 5 then "ID0"+str([Sales])
end