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

Publicado: 17 Jun 2021
Data da última modificação: 16 May 2023

Pergunta

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

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

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

Ambiente

Tableau Desktop 2020.1.0
Windows 10
Google Sheets

Resposta

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

Informações adicionais

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.

Importing Large Number as a String Results in Scientific Notation
Alphanumeric String Values Appear as Nulls (Excel)