Converting numbers to currency capitalization formulas
Publish: 2021-05-01 18:34:50
1. =TEXT(RMB(A1), "[] g& quot;& TEXT(INT(ABS(A1)+0.5%), "[ dbnum2]G/36890;-29992;-26684;-24335;-20803;&& quot;& SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[ dbnum2]035282;020998;; .25972;quot;," quot; (38646;)35282", IF(A1^2< 1," 38646,"] quot; .38646;. quot; 25972;quot;]
2.
We all know that excel can do a lot of accounting work, and there are many excel tips. Today, we will learn how to convert currency numbers into capital letters in Excel. The specific methods are as follows:
1. Select E11, F11 and G11 cells respectively, and input the common expressions in turn: = text (int (ABS (C10)), & quot[ Dbnum2] g / general format & quot;)= TEXTINTABSC10*10-INTABSC10*10,"[ Dbnum2] g / general format & quot;)= TEXTINTABSC10*100 -INTABSC10*10*10,"[ Dbnum2] g / general format & quot;). The meaning of the above formula is to determine the values of yuan, Jiao and Fen of the statistical results and convert them into Chinese capital format
2. Select E10 cell and input formula: = D11 & amp; E11&& quot; Yuan & quot& amp; F11&& quot; Angle & quot& amp; G11&& quot; Score & quot; Or = concatenate (D11, E11, & quot; Yuan & quot;, F11," Angle;, G11, " Score & quot;). The meaning of the above formula is: combine the text in cells D11 to G11 into a whole, and add the unit & lt; Yuan, Jiao, Fen & quot; Words
3. Left click the line label of 11 lines, select the line, right-click the mouse, and select & lt; Hide & quot; Option to hide 11 lines without affecting normal printing. At this point, the conversion work is over, and the effect is as shown in the figure
the above is our method of converting currency numbers into capital letters in Excel. You can use it according to your own needs, and you can also find more tips according to your own intelligence
3.
To use the "user-defined function" of converting numbers to English currency capitalization, the specific use method is as follows:
required materials: Excel, user-defined function of converting numbers to English currency capitalization (can be copied and pasted through the network)
First, open excel file, press Alt + F11 to open VBA window, insert a "mole"
4. "25226;" 24065;"20889021150;" br />20551;"2291414;" 25454;"A21015B136755;" br />=IF(A1=0,""" quot; IF(A1< 0," "" quot; quot; SUBSTITUTES(SUBSTITUTES(INT(ABS(A1),"[ DBNum2]"& quot; " SUBSTITUTES(TEXT(RIGHT(A1*100,2),"[ DBNum2 quot; "" quot; "" quot; "" quot; "" quot; """ quot; """ quot; "" quot; "))
5. 用NUMBERSTRING函数
如：将A2单元格的531转换成大写，则公式
=NUMBERSTRING(A2,2)：此时得出：伍佰叁拾壹
=NUMBERSTRING(A2,1)：此时得出：五百三十一
=NUMBERSTRING(A2,3)：此时得出：五三一
如要将数字读出：则用此公式
=IF(ISERROR(FIND(".",A2)),NUMBERSTRING(INT(A2),2)&"元整",IF(ISERROR(NUMBERSTRING(MID(A2,FIND(".",A2)+2,1),2)),NUMBERSTRING(INT(A2),2)&"元"&NUMBERSTRING(MID(A2,FIND(".",A2)+1,1),2)&"角整",NUMBERSTRING(INT(A2),2)&"元"&NUMBERSTRING(MID(A2,FIND(".",A2)+1,1),2)&"角"&NUMBERSTRING(MID(A2,FIND(".",A2)+2,1),2)&"分"))
如：将A2单元格的531转换成大写，则公式
=NUMBERSTRING(A2,2)：此时得出：伍佰叁拾壹
=NUMBERSTRING(A2,1)：此时得出：五百三十一
=NUMBERSTRING(A2,3)：此时得出：五三一
如要将数字读出：则用此公式
=IF(ISERROR(FIND(".",A2)),NUMBERSTRING(INT(A2),2)&"元整",IF(ISERROR(NUMBERSTRING(MID(A2,FIND(".",A2)+2,1),2)),NUMBERSTRING(INT(A2),2)&"元"&NUMBERSTRING(MID(A2,FIND(".",A2)+1,1),2)&"角整",NUMBERSTRING(INT(A2),2)&"元"&NUMBERSTRING(MID(A2,FIND(".",A2)+1,1),2)&"角"&NUMBERSTRING(MID(A2,FIND(".",A2)+2,1),2)&"分"))
6. Just use the text() function:
= text (490563.23 * 100, & quot[ Dbnum2]
the result shows: four hundred and ninety thousand five hundred and sixty-three yuan and twenty-three cents
= text (490563.23 * 100, & quot[ Dbnum2]
the result shows: four hundred and ninety thousand five hundred and sixty-three yuan and twenty-three cents
7. 20551;"A1e21333;" 20803;"26684;" 20889;"21017;" 20854;"23427;" 21333;"20803;" 26684;"36755br" />=IF(ABS(A1)< 0.005,"& quot; IF(A1< 0," "" IF(INT(ABS(A1)),TEXT(INT(ABS(A1),"[ dbnum2]"& quot; "" IF(INT(ABS(A1)*10)-INT(ABS(A1)*10,TEXT(INT(ABS(A1)*10)-INT(ABS(A1)*10,"[ dbnum2]"& quot; " IF(INT(ABS(A1)=ABS(A1),,IF(ABS(A1)< 0.1," ")" IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),TEXT(ROUND(ABS(A1)*100-INT(ABS(A1)*10,"[ dbnum2]"& quot; "" quot; "))
8.
26041;.65861;65306;
1.-122891;25171;.24320;EXCELL 34920;-26684;65292;A1-36755;-20837;<20889;<-379;<>>>3906999292x22312;";(2037;2037;2037;2626266306530303065;(SUBSTITUTE;) 0.5%," 12367""& amp; TEXT(INT(FIXED(ABS(A1)), "[ dbnum2]G/36890;-29992;-26684;-24335;-20803;&& quot;& amp; TEXT(RIGHT(FIXED(A1),2),"[ dbnum2]035282;/020998;& quot;& amp; IF(ABS(A1)> 1%, " .25972;quot;] quot; (38646;)35282", IF(ABS(A1)< 1," 38646,"] quot; .38646;. quot; 25972;quot;2.122891.30830;<23450;<21518;<25928;<26524;<22914;<22270p>
9. First the content of A1 to B1, then select b1 and right-click to open the drop-down list and select set cell format, as shown in the figure
10. "Nine advantages and seven disadvantages" of RMB appreciation:
advantages: favorable to import; The cost of raw material import dependent manufacturers decreased; The ability of domestic enterprises to invest abroad has been enhanced; The profits of foreign-invested enterprises in China increased; It is beneficial for talents to study and train abroad; The pressure of repaying the principal and interest of foreign debts has been reced; It is more cost-effective to sell Chinese assets; The international status of China's GDP has been improved; Increase the national tax revenue
disadvantages: export is disturbed; Market speculation prevails; New overseas investment decreased; Domestic employment pressure is increasing; Deflation intensified; Instries that are not competitive will be impacted“ In recent years, all kinds of trade disputes against China show that the desire of western countries for RMB appreciation is becoming a practical action, and the discussion on the reform of RMB exchange rate formation mechanism is more and more in-depth
since the second half of last year, four scholars, song Yichang, Professor of science and Technology Publishing House, Chen Xiankui, doctor of Renmin University of China, Xin Xiangyang, researcher of Capital Institute of social and economic development, and Xu keen, head office director of instrial and Commercial Bank of China, have studied the strategic issues of RMB exchange rate. According to their newly completed report, the result of weighing the advantages and disadvantages of RMB appreciation is "more disadvantages than advantages"
the benefits of RMB appreciation
the benefits of RMB appreciation can be summarized in the following nine aspects
1. The decrease in the price of foreign procts is concive to increasing the import of foreign consumer goods and proction equipment, and improving the living standard and instrial level of Chinese people
2. The proction cost of the manufacturers with higher proportion of imported raw materials will decrease. In some areas open to the outside world (such as Shanghai), 50% of the cost of their exports use imported raw materials. They do not like the depreciation of the RMB
3. In order to develop sales channels, Chinese enterprises will expand overseas investment< The appreciation of RMB will increase the profits of foreign-funded enterprises that have invested in China, which will encourage foreign investors to make additional investment or reinvestment. With the graal opening up, RMB appreciation will attract foreign investment into China's securities market, and the proportion of indirect investment is expected to rise
5. In the non manufacturing instry, such as science, ecation, culture and health, it will be of great significance. For example, the appreciation of RMB is concive to talents' study and training abroad, and more families will be able to send their children overseas to study at their own expense; It is concive to the expansion of international tourism, and after the tourist visa reaches a certain standard, it is a business visa, which can promote China's foreign investment
6. The amount of local currency needed to repay the principal and interest of the outstanding foreign debt is correspondingly reced, which can alleviate the debt burden to a certain extent
7. Reasonable valuation of Chinese assets. China's low price of labor and assets is formed in a certain historical stage, and it is bound to be adjusted in a certain historical stage. China must adjust its monetary policy and asset prices while adjusting its import and export policies
8. It is concive to the position of China's GDP in the international economy. The proportion of China's GDP in the world economy (in terms of US dollar) was 2.5% in 1980, 1.8% in 1990 and 3.4% in 1998. The reason is that the exchange rate of RMB against US dollar depreciated to 1 / 5 between 1980 and 1994. As long as RMB appreciation, China's GDP will surpass Germany and France and become the third largest economy in the world
9. Increase national tax revenue. The essence of low exchange rate is that the whole people subsidize the interests of exporters through export tax rebate. This is not very logical for China, the world's second largest foreign exchange reserve country< The disadvantages of RMB appreciation are as follows: 1. Under the current RMB exchange rate, the annual growth rate of China's exports is between 23% and 27%, and that of China's imports is between 19% and 24%. After the appreciation of RMB, the export growth rate may be around 6%, not zero
2. It may lead to the prevalence of speculation. As the national wealth represented by the US dollar has increased rapidly, the stock market and real estate have reached a climax. The development of the "bubble economy" and the polarization continue to expand may cause a blow to the economic development.
3. It may worsen the domestic investment environment and rece the new overseas investment, because this kind of investment becomes relatively expensive. On the other hand, international hot money speculation may increase< At present, most of China's export procts are labor-intensive procts with low technology content, and the export obstruction will inevitably increase the employment pressure< 5. The pressure of deflation. After the appreciation of RMB, China's imports increased and exports decreased, and domestic proction enterprises were further hit< Many uncompetitive instries will be directly impacted. For example, in the field of agriculture, there is a big price gap between domestic and foreign prices of many basic crops. Even with the current exchange rate unchanged, the opening of the market after China's accession to the WTO has made people nervous. The steel, chemical and other raw material instries, which are dominated by state-owned enterprises, also lack competitiveness
7. Japan is an example. After the war, the ratio of yen to us dollar was 350:1 at the earliest, then 250:1, and the highest was 70:1. As a result, the Japanese were so rich that they could not spend money and did not have the ability to continue to accumulate capital. v
advantages: favorable to import; The cost of raw material import dependent manufacturers decreased; The ability of domestic enterprises to invest abroad has been enhanced; The profits of foreign-invested enterprises in China increased; It is beneficial for talents to study and train abroad; The pressure of repaying the principal and interest of foreign debts has been reced; It is more cost-effective to sell Chinese assets; The international status of China's GDP has been improved; Increase the national tax revenue
disadvantages: export is disturbed; Market speculation prevails; New overseas investment decreased; Domestic employment pressure is increasing; Deflation intensified; Instries that are not competitive will be impacted“ In recent years, all kinds of trade disputes against China show that the desire of western countries for RMB appreciation is becoming a practical action, and the discussion on the reform of RMB exchange rate formation mechanism is more and more in-depth
since the second half of last year, four scholars, song Yichang, Professor of science and Technology Publishing House, Chen Xiankui, doctor of Renmin University of China, Xin Xiangyang, researcher of Capital Institute of social and economic development, and Xu keen, head office director of instrial and Commercial Bank of China, have studied the strategic issues of RMB exchange rate. According to their newly completed report, the result of weighing the advantages and disadvantages of RMB appreciation is "more disadvantages than advantages"
the benefits of RMB appreciation
the benefits of RMB appreciation can be summarized in the following nine aspects
1. The decrease in the price of foreign procts is concive to increasing the import of foreign consumer goods and proction equipment, and improving the living standard and instrial level of Chinese people
2. The proction cost of the manufacturers with higher proportion of imported raw materials will decrease. In some areas open to the outside world (such as Shanghai), 50% of the cost of their exports use imported raw materials. They do not like the depreciation of the RMB
3. In order to develop sales channels, Chinese enterprises will expand overseas investment< The appreciation of RMB will increase the profits of foreign-funded enterprises that have invested in China, which will encourage foreign investors to make additional investment or reinvestment. With the graal opening up, RMB appreciation will attract foreign investment into China's securities market, and the proportion of indirect investment is expected to rise
5. In the non manufacturing instry, such as science, ecation, culture and health, it will be of great significance. For example, the appreciation of RMB is concive to talents' study and training abroad, and more families will be able to send their children overseas to study at their own expense; It is concive to the expansion of international tourism, and after the tourist visa reaches a certain standard, it is a business visa, which can promote China's foreign investment
6. The amount of local currency needed to repay the principal and interest of the outstanding foreign debt is correspondingly reced, which can alleviate the debt burden to a certain extent
7. Reasonable valuation of Chinese assets. China's low price of labor and assets is formed in a certain historical stage, and it is bound to be adjusted in a certain historical stage. China must adjust its monetary policy and asset prices while adjusting its import and export policies
8. It is concive to the position of China's GDP in the international economy. The proportion of China's GDP in the world economy (in terms of US dollar) was 2.5% in 1980, 1.8% in 1990 and 3.4% in 1998. The reason is that the exchange rate of RMB against US dollar depreciated to 1 / 5 between 1980 and 1994. As long as RMB appreciation, China's GDP will surpass Germany and France and become the third largest economy in the world
9. Increase national tax revenue. The essence of low exchange rate is that the whole people subsidize the interests of exporters through export tax rebate. This is not very logical for China, the world's second largest foreign exchange reserve country< The disadvantages of RMB appreciation are as follows: 1. Under the current RMB exchange rate, the annual growth rate of China's exports is between 23% and 27%, and that of China's imports is between 19% and 24%. After the appreciation of RMB, the export growth rate may be around 6%, not zero
2. It may lead to the prevalence of speculation. As the national wealth represented by the US dollar has increased rapidly, the stock market and real estate have reached a climax. The development of the "bubble economy" and the polarization continue to expand may cause a blow to the economic development.
3. It may worsen the domestic investment environment and rece the new overseas investment, because this kind of investment becomes relatively expensive. On the other hand, international hot money speculation may increase< At present, most of China's export procts are labor-intensive procts with low technology content, and the export obstruction will inevitably increase the employment pressure< 5. The pressure of deflation. After the appreciation of RMB, China's imports increased and exports decreased, and domestic proction enterprises were further hit< Many uncompetitive instries will be directly impacted. For example, in the field of agriculture, there is a big price gap between domestic and foreign prices of many basic crops. Even with the current exchange rate unchanged, the opening of the market after China's accession to the WTO has made people nervous. The steel, chemical and other raw material instries, which are dominated by state-owned enterprises, also lack competitiveness
7. Japan is an example. After the war, the ratio of yen to us dollar was 350:1 at the earliest, then 250:1, and the highest was 70:1. As a result, the Japanese were so rich that they could not spend money and did not have the ability to continue to accumulate capital. v
Hot content