Text function; Automatical conversion to scientific notation

Hy everyone!
I'm encountering a problem with converting my values to text with the TEXT() function.

My problem:

When the value is 10,000,000 and I use the text() function, there is no problem. The text line item will show "10.000.000".
When the value is 10,000,000.15 (with 2 decimals <> 0), The text line item wil show '1.e7' instead of "10,000,000.15"

This causes problems with my export to other systems. Does anyone of you has encounterd the same problem /has found a workaround?

Kind regards,
Ralf

Accepted answers

All comments

Egor

Hi Ralf,

If in case when the value is with 10,000,000.15 you still want to see 10000000 in your text filed, the easiest workaround would be a ROUND function:

TEXT(ROUND('Your number', 0))

If you want to see the decimas in text field as well, it is a bit more compex, but also possible via concatination.

Best regards,

Egor.

PaulRitner

This is an odd error...!

I was playing with converting 10000000.15 to TEXT and arrived at the following formula using Egor's concatination suggestion (which looks like the only way to get the whole value, with decimals, converted):

TEXT(ROUND(Amount, 0)) & "." & MID(TEXT(ABS(ROUND(Amount, 2) - ROUND(Amount, 0))), 3, 2)

Stranger yet was that, when converting the decimal, the .15 had a strange residual value 10 or so places out... even though the text input was keyed in as 10000000.15

Question: Is the export format other than CSV? If you are exporting to a csv/flat file, then there's no need to convert the value to text prior to export (because it will become a text format in the export file).

Hayk

This error comes from calculations.

Calculation goes crazy with some values. If you try to multiply 10000000.15 by 1, anaplan shows 1.000000015E7 (61.2 * 3 gives 183.60000000000002, but 61.2*10*3/10 = 61.2).

I assume it can be somehow related to floating-point arithmetic.

RalfdeGier

Dear Paul,

THank you for the formula, I will give it a go!

In order to export to other systems, i need to export in CSV. The problem is that I need to have a semicolon as a seperator, instead of a comma. As stated in an ealier topic, I need to convert everything to a string text item which then used a semicolon instead of a comma. Therefore I need to transfer all values to text.

That's the reason I have encountered this error in Anaplan!

abarnett

Thank you for this! I use a variant for my particular use case due to the last value sometimes showing as a period with the above formula. It seems to work with all real world numbers I encounter (though there are some extremely large numbers I have tested that still do not work:
IF
RIGHT(TEXT(ROUND(Amount, 0)) & "." & MID(TEXT(ROUND(Amount, 6) - ROUND(Amount, 0)), 3, 6), 1) = "."
OR LEFT(RIGHT(TEXT(ROUND(Amount, 0)) & "." & MID(TEXT(ROUND(Amount, 6) - ROUND(Amount, 0)), 3, 6), 3), 1) = "E"
THEN
TEXT(ROUND(Amount, 0))
ELSE
LEFT(TEXT(ROUND(Amount, 6)), LENGTH(TEXT(ROUND(Amount, 0))) + 7)

rob_marshall

@abarnett ,

Please break that up into multiple line items as a) performance will be better and b) you will actually be able to figure out what is not working.

Thanks,

Rob

Quick Links

Can you help?

Unanswered questions

Not Able to Join the Level 1 Model Building Course
Hey Everyone, I was trying to learn about the Level 1 Model Building Course, when i log in to my system and click on "Start the course" option its showing training unavailable, Can anyone please help me with this @Dikshant and all?
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…