Excel Amount Lowercase Capitalization formula

Recently turned to several Excel related forums, found that some netizens in the search for the amount of money converted into renminbi capitalization method. Out of curiosity, I searched the internet for a few, what VBA, add-ins, formula functions, all kinds of ways to debut, everything. But my habit is, as long as the formula to solve the problem, resolutely do not use VBA. So I am particularly concerned with the use of formulas to generate the method, the result is a formula for the method is more brilliant colorful, dazzling people.

By analyzing the more than 20 formulas I collected, I found that there were three formulas for my taste, which I listed here.

Formula One:

Substitute (Substitute (A1 0, negative, "") &text (TRUNC (ABS (ROUND)), "[a1,2]") & "Yuan" DBNum2 (Find (). ", ROUND (a1,2))," ", TEXT (TRUNC (ROUND (a1,2) *10))," [DBNum2] ") &if (Iserr (Find (". 0 ", Text (A1," 0.00 "))," Corner " , "") &if (left (ROUND (a1,2), 3) = ". ", TEXT (Right (ROUND (a1,2))," [DBNum2] ") &" Min ", IF (ROUND (a1,2) = 0," "," whole "))," 0 yuan 0 "," ""), "0 yuan", "" "

Formula Two:

Concatenate (IF (A1 0, "negative", ""), TEXT (if (TRUNC (A1) =0, "", TRUNC (ABS (A1)), "[DBNum2]"), IF (INT (TRUNC (A1)) = 0, "", "Yuan"), TEXT (IF or (ABS (A1) 0.1,trunc (A1) =a1), "", Right (TRUNC (a1*10), 1)), "[DBNum2]"), IF (Right (TRUNC (a1*10), 1) = "0", "", "Corner" ), TEXT (if (TRUNC (a1*100), 1) = "0", "", Right (TRUNC (a1*100), 1), "[DBNum2]"), if (Right (TRUNC (a1*100), 1) = "0", "", "", " Points "))

Formula Three:

if (ROUND (a1,2) =0, "", if (ROUND (ABS (A1), 2) =1,text (INT (ROUND (ABS (A1), 2)), "[DBNum2]") & amp; Yuan "," ") &if (right TEXT (A1,. 00"), 2) *1=0, "whole", IF (Right (text (A1, ". 00"), 4) *1 =1,if (Right (text (A1, ". 00"), 2) *1 "9," "," 0 "), IF (ROUND (ABS (A1), 2)" = 1, "0", "")) &if (Right (text (A1, ". 00"), 2) *1 9,text (Right (text (A1, ". 00"), 2), " [DBNum2] ") &" Corner "," ") &if (Right (text (A1,. 00)) *1 0,text (Right (text (A1,.))," [DBNum2] ") &" Min "," whole "))

I've tested these three formulas in a number of different numbers, and I've met my requirements, and at least I haven't found anything wrong with the formula so far. The above formula takes a different approach to the treatment of negative numbers. Formula One and Formula Two pair negative numbers are preceded by a "negative" word, and Formula Three is treated by positive number. I prefer Formula Three, is not there a conditional format can be set? If a negative number is changed to red with conditional formatting.

I am not prepared to explain the above formula in this paper. The purpose of writing this article is that I also want to "assemble" a so-called universal formula myself. I'm just saying "assemble" instead of "development" or "patchwork", because Excel's functions are readily available, and when used is to achieve a certain purpose, according to its inherent rules, the organic combination of it is called "assembly." But in this process, but also to use their brains, not casually "patchwork" can be successful. The purpose of this article is to show me the idea of "assembling" the formula.

What would you consider if you were to write a renminbi amount to capitalize on a programming language, such as C? I think every digit should be taken into account. In Excel, however, there is a "special" format in the cell format, which has a format for converting lowercase numbers to uppercase digits. Note, however, that the conversion here is a number rather than an amount. Therefore, there is no "yuan", "Corner", "Minutes", "whole" and so on in the capital amount of words. However, "meta" only appears after the integer portion, while the fractional part is only two digits in the corner. This is done by using Excel's special format to divide the data into integral parts, corners, and three parts. The order of processing is the first integer, then the corner, and then the decimal point.

Due to the three parts to be dealt with separately, and then according to the situation with the "Yuan", "Corner", "points" and "whole" connection. You can connect by using the concatenate () function, such as Formula Two, or you can use the connector &. With & to connect, the formula structure seems to be relatively simple and clear, so I chose to connect with &.

Although the problem is decomposed, there are quite a few details to consider.

If the cell is 0 or empty, the output is empty, avoid the words "0", "0 yuan" and so on. Do not process the non-numeric characters, using Excel's own error prompts.

Negative numbers are treated in positive numbers, then converted to red in conditional format, and do not use the form of "negative xxxxxx".