KNOWLEDGE BASE

Numbers Converted to Strings when Exporting to Crosstab


Published: 08 Aug 2014
Last Modified Date: 09 Nov 2017

Issue

When you export a view as a crosstab from Tableau Server, numbers that use a comma as a thousand separator (such as 1,200) might be converted to strings in Microsoft Excel.

Environment

  • Tableau Server
  • Microsoft Excel
  • Non-US locale setting for the Tableau Server user

Resolution

Option 1

  1. In Microsoft Excel, select the column that contains the strings that should be numbers, and then open the Find and Replace dialog box. 
  2. In the Find and Replace dialog box, make the following selections, and then click Replace All.
    • For Find what, press ALT and type 0160, using the number keypad.
    • For Replace with, leave the field blank.
The incorrectly formatted strings are replaced with numbers.

Option 2

  1. Download the workbook from Tableau Server.
  2. Open it in Tableau Desktop and export the data as crosstab (Worksheet > Export > Crosstab to Excel)
The downloaded Excel document should contain figures rather than strings.

Option 3

As an alternative workaround, work with your IT team to change your computer's local setting. 
  1. Open the Windows Region and Language settings.
  2. Change Format to match the crosstab formatting, e.g. English (South Africa).
  3. Select Apply.
  4. Open .csv exported from Tableau Server.

Cause

In certain locales, thousand separators are exported from Tableau Server as non-breaking spaces. When Microsoft Excel encounters a thousand separator in a number that is denoted by a non-breaking space, it treats that number as a string.
Did this article resolve the issue?