TEXT() function to follow Excel rules

TEXT() function to follow Excel rules

Being a Anaplan builder, I would like to extend TEXT() functionality to work similarly to excel function, exactly:


1) Follow different format conversions

2) Follow preset patterns, to exclude unexpected text from numbers results etc.


Example of wrong work to be prevented with new functionality: 

Text(1504322.40)= 1504322.4 - ok

Text(15751942)= 15751942 - ok

Text(15751942.50)= 1.57519425E7 - wrong


With same functionality, as in Excel, it would be possible to use preset patterns to exclude such cases. Examples from original Excel TEXT() function description (taken from support.office.com) :

Formula                                                 Description


Currency with a thousands separator and 2 decimals, like $1,234.57. Note that Excel rounds the value to 2 decimal places.


Today’s date in MM/DD/YY format, like 03/14/12


Today’s day of the week, like Monday


Current time, like 1:29 PM


Percentage, like 28.5%

=TEXT(4.34 ,"# ?/?")

Fraction, like 4 1/3

=TRIM(TEXT(0.34,"# ?/?"))

Fraction, like 1/3. Note this uses the TRIM function to remove the leading space with a decimal value.


Scientific notation, like 1.22E+07

=TEXT(1234567898,"[<=9999999]###-####;(###) ###-####")

Special (Phone number), like (123) 456-7898


Add leading zeros (0), like 0001234

=TEXT(123456,"##0° 00' 00''")

Custom - Latitude/Longitude

Benefit of the change: flexible TEXT() function with ability to prevent unexpected results.

The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.
1 Comment
Community Manager
Status changed to: Needs Community Support