Many times, it is necessary to display content in Tableau in a verbose manner. For example: displaying comments from a survey or showing lengthy project descriptions or calls to action in an executive dashboard.
Connecting Tableau to a database with long string fields or textarea fields results in no issues.
However, when working from an Excel spreadsheet or Microsoft Access, any string entered into a field that is longer than 255 characters gets automatically trimmed in Tableau. This is caused by the Microsoft JET Engine Driver for Excel and Access, which has the limitation to 255 characters.
If I enter a long string into an Excel field and then connect to Tableau, I get the following results:
Ex. 1 & 2 – Excel spreadsheet with three rows, each with long string values. Tableau only renders the first 255 characters of each string dimension due to the limitations from the JET Engine Driver.
I want to be able to show the entire string field in Tableau. Fortunately, there is a neat workaround to reach the desired results in Tableau.
The result requires two tricks. One in Tableau, and one in the Excel spreadsheet itself.
Create two or more new columns in the Excel spreadsheet. For my example, I have added three columns, named “First Part”, “Second Part”, and “Third Part.” In each of these columns enter a formula that grabs different chunks from the Long Description field and separates them.
My Long Description is in column ‘C’. The formulas are as follows:
Column and Formula
First Part: =LEFT(C2,255)
Second Part: =MID(C2, 256, 255)
Third Part: =MID(C2, 512, 255)
Ex. 3 – The resulting columns parse the original Long Description field and only keep the parts limited by the formulas.
After saving the spreadsheet, refresh the view in Tableau.
In order to get all of the parts of the Long Description into one field, common sense would say to simple concatenate the three resulting strings. Unfortunately, the JET Engine Driver will still limit the resulting calculation to 255 characters.
The workaround requires a simple table calculation:
Ex 5. – The resulting visualization in Tableau with the Table Calculation.
*Since this is a table calculation, make sure the direction and scope of the calculation is set to ‘CELL’.