Why is there a round at the end of a number converted from text?

Hello,

I am converting a text into a number to do a complex sort. It adds code from several different dimensions to create a large text. Then I convert it using value. When I do this, the final 3 digits are being rounded unnecessarily. Does anyone know how to get past this?

Original text: 23921101010004270452

Converted to number using VALUE: 2.3921101010004270000

In fact, when I paste it here, I am noticing that decimal in front. It's also something I don't want.

Accepted answers

KirillKuznetsov

Hi @DeepakK

I tried to paste your value from original text to a number formatted line item and in the small preview window I can see that the value is interpreted as in scientific format in Excel

I believe your number is too big.

Funny that excel is also can't deal with such big number… Tried to paste into a cell in Excel:

But as I understand for sorting you still can use text format. Can you tell what you are trying to achive?

Did you consider using A-Z instead of at least part of your numbered code?

All comments

KirillKuznetsov

Hi @DeepakK

I tried to paste your value from original text to a number formatted line item and in the small preview window I can see that the value is interpreted as in scientific format in Excel

I believe your number is too big.

Funny that excel is also can't deal with such big number… Tried to paste into a cell in Excel:

But as I understand for sorting you still can use text format. Can you tell what you are trying to achive?

Did you consider using A-Z instead of at least part of your numbered code?

MarkWarren

This page explains the issue…
https://help.anaplan.com/precision-differences-and-large-number-calculations-f8b2ba0a-1291-4424-9a06-06a601ccc269

Dikshant

I tried and faced the same issue

The line item starts with 2392, but the top right side section shows 2.392.

Anaplan has a 15-character limit for decimal places. If you observe after the 15th character, it makes everything 0. So I guess this is an anaplan limit of decimal places. Since you mentioned you are using this for sorting, so I will suggest you to calculate the length of this code. For those which is less or equal to 15 character, give the sort. For those which are greater than 15 character, split the number (line item which is in Text format) into two parts.

So taking this number as an example, 23921101010004270452 split it into two

Part 1 239211010100042

Part 2 70452

For all such codes you get two parts of code. Then you rank part 1 and part 2 individually and combine them with a dot as separate and convert it into number.

Part 1	Rank Part 1	Part 2	Rank Part 2	Final Rank
239211010100042	1	70452	1	1.1
239211010100042	1	70453	2	1.2
239211010100044	3	70454	1	3.1
239211010100045	4	70455	1	4.1
239211010100046	5	70456	1	5.1

Just an idea - See what's better with your requirement 😊

DeepakK

Great suggestions! Will try this out today and let you know. I noticed that it is taking a decimal too. I never intended a decimal to be there.

DeepakK

Update: I resolved this by using the text itself to Sort. Thanks to @KirillKuznetsov for that suggestion. I thought it had to be a number.

I also put the larger sort item at the end. It's hard to explain this but the Job code that I am using will have at least 4 characters. By putting this at end instead of a 0/1 boolean, the rounding never happened. Not sure why this is but it works.

Thanks all for the help!

Quick Links

Can you help?

Unanswered questions

A reminder of Bulk Copy - Current year to Prior year version for YoY analysis
A quick reminder of the Bulk Copy functionality. Bulk Copy allows you copy large volumes of data from one slice of a model to another in a single, optimised operation, instead of using formulas or imports. Use case: copy a version (RF1) into a prior year version (PY RF1) using a versions list to allow for year-on-year…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385