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.
