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.
Best Answer
-
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?
5
Answers
-
This page explains the issue…
0 -
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 😊
0 -
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.
0 -
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!
1
