Register

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

=TEXT(1234.567,"$#,##0.00")

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

=TEXT(TODAY(),"MM/DD/YY")

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

=TEXT(TODAY(),"DDDD")

Today’s day of the week, like Monday

=TEXT(NOW(),"H:MM AM/PM")

Current time, like 1:29 PM

=TEXT(0.285,"0.0%")

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.

=TEXT(12200000,"0.00E+00")

Scientific notation, like 1.22E+07

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

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

=TEXT(1234,"0000000")

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.

4 Comments
Miran
Community Manager
 
Status changed to: Your support is needed
Gwen.pryor
Regular Contributor
 
Status changed to: Under Investigation
chrised209
Contributor

I'd be happy enough with being able to apply the native Anaplan Number formatting in the referenced line item instead of only producing a flat number.

 

How about:

 

TEXT(x [, Format])

 

x:           Number: Numeric line item, property, or expression

Format: Text:       Excel-style formatting string as articulated above, with no change applied by default to numeric expressions and properties and native Anaplan Number formatting applied to line item referenced values. 

dafinkapancheva
Group Leader - Employee

Hi there,

 

We have released a new option to export formatted numbers for unformatted file types: text, CSV, unformatted XLS, and XML. The exported numbers match the number of decimal places set up for the line item, and use "." as the decimal indicator. No thousand separator is used, and custom units and currency symbols are not exported. 

 

This improvement should address the key pain-point described in the idea.

New Solutions
Anaplan Platform
DMManalili
Frequent Contributor