KNOWLEDGE BASE

How to format a column contains long numbers and text from Google Sheets


Published: 17 Jun 2021
Last Modified Date: 18 Jun 2021

Question

When importing a data source from Google Sheets to Tableau Desktop, if a column contains long numbers and text strings at the same time, the long numbers will be converted into scientific notation format when selecting the field format as "String".
User-added image      User-added image       

If changing the field format to "Number (whole)", the text value will show as "Null" while the long numbers are displayed properly.
User-added image

Neither of the formats type can make both long numbers and text displayed properly.

Environment

  • Tableau Desktop 2020.1.0
  • Windows 10
  • Google Sheets

Answer

1. Select the entire column in Google Sheets;
2. Select Format->Number, change "Automatic" to "Plain Text";
User-added image
3. In Desktop, refresh the data source
User-added image
4. Make sure the current field format is "String". 
User-added image
 

Additional Information

The cause is when Tableau Desktop forces these long numbers to convert to scientific notation when importing large numbers as strings. However, when there is also text in the same column, simply changing the field format on Tableau Desktop cannot make both long numbers and text displayed properly. 

 
Did this article resolve the issue?