An Excel function for translating numeric expressions into English language equivalents, with built-in intelligence which is capable of handling a wide range of numeric inputs, and which can handle English grammar and currency-specific words correctly, and, of course, very easy to use and requires minimal user input, have long been a dream of a lot of spreadsheet users and solution developers.
Number->Text Wizard is your solution. It is the best number to English translation tool for the Microsoft Excel spreadsheet environment. It is absolutely the best and the most versatile ever developed tool of its type for the spreadsheet.
The components -
Number->English Translation Wizard is divided into two main components :
- The Number->English Translation Wizard
- The NUM2TXT worksheet function
You can access the “Number->English Translation Wizard” from the “Expander” menu, under the “Text Manipulation” submenu.
After initializing the tool, you’ll see a well-organized interface, in which you can decide how you would want the numbers to be translated.
The options for controlling the translation output are systematically grouped into 4 categories :
1. General Options For the control of the handling of decimal places in numbers 2. Case Options For the control of upper, lower, or proper cases in the translation output. 3. Currency Options For the assignment of currency-specific text strings to the translation output. 4. Output Location The location where the translation output will be placed.
In addition to the powerful facilities, the Number->Text Wizard also provides you with a “testing area” in its interface, in which you can test input some numbers and have a rough idea about how the choices you made would affect the translation output.
The Number->English Translation Wizard has the ability to “remember” all your previous settings and automatically restore them the next time you use the function.
In the full (registered) version of Excel Power Expander, there is a new worksheet function NUM2TXT().
Function of NUM2TXT() -
Translates a number into English Language text.
- Absolute value of the number input must be smaller than one thousand nonillion. (US English definition : 1033) (i.e. -1E+33<x<1E+33)Under Excel 5 and 7, length of translation output must not exceed 255 characters, which is the maximum length a cell can accommodate. Under Excel 8 (Excel 97), length of translation output must not exceed 32,000 characters
- The language translation adopts the US English convention.
SyntaxNUM2TXT(number, frac, places, dollar_txt, cent_txt, output_case, smart_case)
number is the number to be translated into English. It can be either a positive or a negative number; with/without decimal places. It may also be in scientific format, e.g. 234E+17frac is a number either 1, 2, or 3. It determines the way decimal places (, if any,) are being treated. Default value is 1. (Optional)
Set frac equals to If decimal places exist 1 or omitted Translated into a fraction. e.g. 0.27 = 27/100 ; 0.12345 = 12345/100000 2 Translated into English words. e.g. 0.27 = zero point two seven 3 Completely ignore any decimal places. e.g. 1.6 = one
place determines how many decimal places (,if any,) to which you want to round number. (Optional)dollar_txt is the text string to be placed before the decimal places. (Optional) For example, when translating the number 123,
dollar_txt Translation output Dollar One-Hundred Twenty-Three Dollar Pound One-Hundred Twenty-Three Pound
cent_txt is the text string to be placed after the decimal places. (Optional)For example, when translating the number 123.25,
dollar_txt cent_txt Translation ouput Dollar Cent One-Hundred Twenty-Three Dollar Twenty-Five Cent Pound Pence One-Hundred Twenty-Three Pound Twenty-Five Pence
output_case is a number either 1, 2 or 3. It determines whether the output text string is in upper, lower, or proper case. Default value is 1. (Optional)
Set output_case equals to Effect 1 or omitted Output text in UPPER case 2 Output text in lower case 3 Output text in Proper case
smart_case is a Boolean value either TRUE or FALSE. It determines whether the word AND should always be in lower case. Default value is FALSE. (Optional)
Set smart_case equals to Effect TRUE The word and always in lower case FALSE or omitted The word and follows the value of the variable output_case.
NUM2TXT translation employs the following definitions :
- one billion = one thousand million = 1E+9one trillion = one thousand billion = 1E+12one quadrillion = one thousand trillion = 1E+15 one quintillion = 1E+18one sextillion = 1E+21one septillion = 1E+24 one octillion = 1E+27
- one nonillion = 1E+30