This formula relies on the use of four key functions:
It’s not necessary to dissect every component of the formula, we only need to decipher the first bit highlighted below.
&IF(--LEFT(TEXT(B3),"000000000.00"))=0,,IF(AND(--MID(TEXT(B3),"000000000.00"),2,1)=0,--MID(TEXT(B3),"000000000.00"),3,1)=0)," Hundred"," Hundred and "))
Once we have this portion figured out we’ll be able to figure out the remainder of the formula since it is very much a repeated operation.
The LEFT Function
The purpose of the LEFT function is to extract a specific number of characters from the text starting from the left side. The structure of the LEFT function is as follows.
The parameter “text” refers to the cell holding the input, while “[num_chars]” indicates the number of characters to extract. Because the “[num_chars]” parameter is optional, skipping this parameter will result in a default extraction of 1 character.
The MID Function
The purpose of the MID function is to extract a specific number of characters from the text starting from a specific character position (counting from the left side). The structure of the MID function is as follows.
=MID(text, start_num, num_chars)
The parameter “text” refers to the cell holding the input; the parameter “start_num” indicates the position to begin text extraction (counting from the left side), while “num_chars” indicates the number of characters to extract.
The TEXT Function
The purpose of the TEXT function is to represent a cell’s information with specific formatting. The structure of the TEXT function is as follows.
Normally, formatting is applied to a cell through traditional cell formatting controls.
The TEXT function applies formatting (fancy word alert) formulaically. This way, we can change the formatting of information dynamically based on the need of the moment.
EXAMPLE: Suppose you have a calculation that needs to reflect U.S. Dollars or Euros depending on a country selection. The formula could look something like the following:
=IF(Country=”USA”, TEXT(Sale, ”$#,##0.00”), TEXT(Sale, “€#,##0.00”))
If the user selects USA (cell A2) , they get the total sales represented by a “$” sign (cell C4) . If the user selects any other country, the total sales is represented by a “€” symbol.
This function relies on a cell named “Country” (cell A2) where a user may select a country from a Data Validation dropdown list, and a cell named “Sale” (cell A9) that may hold something like a SUM function that adds all the sales together.
In our specific example, the function…
…pads the input number with leading zeroes. By counting the leading zeroes, the formula will be able to determine if the number is in the hundreds, thousands, or millions.
If a user inputs a number, such as 123456.78, the TEXT function will interpret the number as 000123456.78. The three leading zeroes indicates a number in the thousands.
The CHOOSE Function
The CHOOSE function is used to simplify multiple nested IF functions that are examining the same data. CHOOSE is especially useful when working with indexes.
The purpose of the CHOOSE function is to select a value from a built-in list of values based on a supplied number.
=CHOOSE(index_num, value1, [value2], …)
If we were to extract the first (left-most) number from the input number and give it to the following CHOOSE function, what do you think we would get back from the CHOOSE function?
=CHOOSE(LEFT(B3), “One”, “Two”, “Three”, “Four”, “Five”, “Six”, “Seven”, “Eight”, “Nine”)
We would see the word version of the extracted number. “2” would yield “Two”, “5” would yield “Five”, etc…