Posts From September, 2015

Tableau Export Crosstab - getting large numbers to 'look right'

Here’ a trick to use when Tableau’s Export Crosstab has String fields filled with large numbers. As you likely have found out, when the csv is opened by Excel, the Microsoft Jet Engine interprets to the default ‘General’ cell formatting. A Tableau string field with numbers is converted to Excel number values, with the result that leading zeros are dropped and, if it is a large number, being displayed in Scientific notation. This is typically not what users want to see for fields like account numbers. What they want is for the string number to come across into a formatted text cell, but that won’t happen.

So, an alternative is to create a text version dimension of the field by appending it with a non-printing character. The character I’ve used is the ‘En Quad’, which can be found in MS Word by inserting a Symbol, then typing 2000 in the character code field in the dialog box as shown below. Once the character is selected, then click Insert to place it into a MS Word document. For ease of finding this character, I typically insert it between two visible characters, like the !@ shown below.

Highlight the character between the two visible, copy it and then paste that into your Tableau calculated field equation, which would be something like this:

// Appends non-visible character to string “ ” + [field]

Use the new calculated field in your worksheet and when exported, the MS Jet Engine will interpret the column as text, showing the entire large number, even with leading zeros.

MS Symbol Dialog:
Smiley face

Sample insert between two visible characters:

        !!͏ @

Copyright© Brad Earle
Site Map | Printable View | © 2008 - 2021 KB Earle Associates LLC | |