5 Replies Latest reply on Dec 30, 2016 3:23 PM by Jens Peder Pedersen

# How to avoid a part number is converted to a exponential number?

I have this problem, that Tableau converts a part number to an exponential number, see the screenshot below. The field "Item Number1" is formatted as a text string in the data pane, and the field contains a variation of part numbers, some of which are just numbers, some are a mix of letters and numbers.

How do I avoid that Tableau converts the PN text to an exponential number ???

• ###### 1. Re: How to avoid a part number is converted to a exponential number?

Could you provide a sample twbx or sample data?

• ###### 2. Re: How to avoid a part number is converted to a exponential number?

Jens,

Without seeing your data or workbook this may be expected due to the data type. Perhaps it won't fit the entire number in that column so that is why it is 'converting' it?

Can you try moving the column to see if the number is still displayed?

Since it's a string, you can try and use a LEFT() in a calculated field if you only need part of the number?

1 of 1 people found this helpful
• ###### 3. Re: How to avoid a part number is converted to a exponential number?

You can convert them from strings to numbers by using the calculation

FLOAT([Item Number 1])

This will make them into whole numbers rather than exponentials.

However, with those numbers that contain a " - " this will then calculate these (subtracting the first from the second). So you will either need to remove the " - " before conversion (by using a replace function) or use an IF statement that only converts numbers that do not contain a " - ". In that case you will then need to make sure that the field remains a string by using the following in your IF statement, converting it to a number and then to a string again.

STR(FLOAT([Item Number 1]))

It would look something like this

IF

CONTAINS([Item Number 1], "-") THEN [Item Number 1]

ELSE

STR(FLOAT([Item Number 1]))

END

Does that help at all? I hope I understood the problem correctly.

Best wishes

Naledi

1 of 1 people found this helpful
• ###### 4. Re: How to avoid a part number is converted to a exponential number?

Thanks, Naledi - the screendump I included is probably a little misleading. The part numbers are basically unique alphanumeric, could be "12345" or "666-21" or "MC10-411-3" so they are not numbers and you can not do calculations on them.

I will try if the STR() function or LEFT() like Matthew suggested works.

• ###### 5. Re: How to avoid a part number is converted to a exponential number?

Thanks for your assistance with my question. I tried the STR() and LEFT() functions, but they did not fix the problem - so I investigated the data files (which I should have done in the first place). It turns out the exponential conversion happened in the input excel file because excel mistook some (but not all) of the PN as numbers.