Make Excel RMB lowercase uppercase

Steps/Methods

To convert the RMB lowercase amount to uppercase, in the format provided by Excel, change the [dbnum2]g/General format] in the custom format type to the [dbnum2] g/Universal format "" ". However, there are problems in converting decimals, such as ￥6,050.09 can only be converted to "Lu Ying 0". 0 Nine circles. So can excel solve this congenital deficiency? The method has the following two kinds of B1 conversion, assuming that B1 is rounded through the function round () to obtain two-bit decimal number of lowercase amount, its value is ￥6,050.09).

One, in three consecutive row of peers in the cell conversion

1. B2 Input Formula "=if (b1〈0," the amount is negative invalid ", INT (B1))", the result is ￥6,050.00, then click "format → cell → number → special → Chinese capital number → OK", B2 show "Lu Ying 0 Wu-pick", and then click on " Format → cell → number → custom, modify the [dbnum2]g/General format] in the type edit box to: [Dbnum2] ("RMB") g/General format "Yuan", at this time B2 display: "(RMB) Lu Ying 0 Wu Yuan". 2. C2 input "=if (b1〈0," ", INT (B1)-int (B1) 10)", the same C2 to: [dbnum2]g/General format "Corner", C2 display: "0 Corner". 3. In D2, enter "=if (b1〈0," ", INT (B1)-int (B1 10) 10)", the same D2 to read: [dbnum2]g/general format "cent", D2 display: "Nine points". This enables conversion of the uppercase amount format in a contiguous three-row cell, as shown in Figure 1. In order for the conversion to conform to the financial format, B2 should be right-aligned, C2 column widths are not spaced, and the left and right border lines are not set, D2 to align.

Second, use Excel to provide the function transformation

Single-digit 0, then do not show, not 0, display "xxx", not 0, and the corner and the location are 0, display "xxx", the point is 0, then "0" integer. The first digit after the decimal point is 0, and the element or the fraction has one 0, the number is not displayed, 0, and the element and the bitwise are not 0, then "0" is displayed, not 0, and "X-Corner" is displayed. The second digit after the decimal point is 0, then the whole is displayed, not 0, and the x points are displayed. According to the above idea in B2 input formula: "If (b1〈0," the amount is negative invalid, if (OR (b1=0,b1= ")," (RMB) 0 Yuan ")); IF (B1〈1, "(RMB)", TEXT (int (B1), "[Dbnum2] (RMB) g/General format") && "Yuan") &&if (int (B1)-int (B1) 10=0); IF (int (B1) (int (B1)-int (B1 10) 10) = 0, "", "0"), TEXT (B1)-int (B1), "[Dbnum2]") && "Corner") &&if ( Int (B1)-int (B1 10) 10) = 0, Integer, TEXT (int (B1)-int (b1*10) *10), [dbnum2]) &&) ". After confirmation B2 show: "(RMB) Lu Ying 0 Wu Yuan points." When used later, copy the formula to the relevant cell, and if the converted object B1 has changed, click edit → replace to replace the B1. The results of the two conversion tests are shown in Figure 2.