Excel RMB lowercase amount conversion capitalization tips

Source: Internet
Author: User
Tags lowercase

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.