Excel RMB lowercase amount conversion capitalization tips

To convert the RMB lowercase amount to uppercase, and 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 types 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).

First, convert

1 in three consecutive cell rows. 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", then click "format → cell → number → custom" , modify the [dbnum2]g/General format] in the type edit box to: [Dbnum2] ("RMB") g/General format "Yuan", at which point B2 shows: "(RMB) Lu Ying 0 Wu Yuan".

2. C2 enter "=if (b1〈0,", INT (b110)-int (B1) 10), and the same C2 to: [dbnum2]g/Common Format Corner, C2 display: "0 corners".

3. D2 enter "=if (b1〈0,", INT (b1100)-int (b110) 10), and the same D2 to: [dbnum2]g/General format "Min", D2 show: "Nine points".

enables conversion of the uppercase amount format in a contiguous three-peer cell. 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.


Two, function conversions provided in Excel

Single-digit 0, does not display, is not 0, displays "xxx Yuan", is not 0, and the corner and the position are 0, displays "The xxx meta integer", the element angle cent is 0, then displays "0 yuan whole".


The first digit after the decimal point is 0, and the unary or bitwise is 0, the 0 is not displayed, the element and the fractional are not 0, then "0" is displayed, not 0, and "x" is displayed.

The second digit after the decimal point is 0, the whole is displayed, not 0, and the x points are displayed.

Enter the formula in B2 based on the above idea:

"IF (b1〈0, the amount is negative),"if (OR (b1=0,b1= ")," (RMB) 0 integer "),

IF (b1〈1," (RMB) ", TEXT (INT (B1), [dbnum2] (RMB) g/general format) &&" Yuan ") & &if (int (b110)-int (B1) 10=0);

IF (int (B1)  (b1100-int) = 0, "", "0"), TEXT (int (b110) 10 ( B1) 10, "[dbnum2]") && "Corner") &&if ((int (b1100)-int (b110) 10) = 0, "whole", TEXT (int (b1100)-int (b1*10) *10 ), "[Dbnum2]"), && ")". After the

Confirmation, B2 displays: "(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.