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.
... View more