Like and see, form a habit; A wise man may sometimes miss.

Wechat search [Yixin Excel] to pay attention to this different we-media person.

In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.

For those of you who are engaged in finance, you will often be asked to display the amount in capitals, as shown in the figure below.

Today, I’m going to share with you how to convert numeric amounts into uppercase amounts.

1. Cell format

Excel itself comes with a Chinese uppercase format, but it’s easy to use. Let’s take a look.

I don’t know if you remember in the cell format there is a special format under the number category category, and under that format there is the Chinese uppercase number format.

Let’s try it out with the data we started with, just to see what it looks like, just to make a comparison.

See the result should know why xiaobian said unsatisfactory bar. There are several problems with this method: (1) there is no unit of dollar Angle; ② There is a decimal point; ③ Does not contain the whole word.

The NUMBERSTRING function

The NUMBERSTRING function is a hidden function in Excel that isn’t in the function list, so we won’t go into why Excel doesn’t expose it.

Function format: “=NUMBERSTRING(VALUE,TYPE)”;

Function function; Process uppercase digits from lowercase digits;

Note: VALUE indicates the number to be converted. TYPE The value can be 1, 2, or 3. Each TYPE corresponds to a display uppercase mode.

Note: this function only supports positive integers, not decimals.

Let’s look at a comparison of the three types:

It turns out that the type argument of 2 is what we want, and we find that there are no units, no whole words, and the function can’t handle decimals. We can see the effect:

And we also found that if we use decimals directly, the results will be rounded.

But that’s not a problem, because it’s a function and we have a lot of room to manipulate it, and we can manipulate it to get what we want.

Let’s deal with the rounding problem first. Today we’ll learn about a new function TRUNC. TRUNC TRUNC TRUNC TRUNC TRUNC TRUNC TRUNC TRUNC TRUNC TRUNC

And then let’s do the decimal part. Since the NUMBERSTRING function can only handle integers, wouldn’t it work if we converted the decimal part to an integer? And we only have to deal with angles and fractions which are two words.

Xiaobian’s first thought is to intercept the function, and then locate the Angle division, the scheme is ok, but the formula is very long and tedious to write.

If we take a two-digit decimal and multiply it by 10, then we cut off the whole number to get 1, then the tenth place becomes the ones place. Then take the integer part of the two-digit decimal and multiply it by 10 to get the amount 2, and the ones digit of the amount 2 must be 0, and the amount 1 and the amount 2 must be the same, just subtract the amount 1 from the amount 2 to get the Angle.

Similarly, “=TRUNC(A5*100)-TRUNC(A5*10)*10” can be scored.

So now that we’ve solved the rounding, the angles, the divisions, let’s just put the formula together. The final formula is: “=NUMBERSTRING(TRUNC(B5),2)&” meta “&NUMBERSTRING(TRUNC(B5*10)-TRUNC(B5 *100)-TRUNC(B5*10)*10 &” Angle “&NUMBERSTRING(TRUNC(B5*100)-TRUNC(B5*10)*10 ,2)&”分”, let’s see the effect:

See the result is still not very good, there are still a lot of problems, the end of the integer should be “whole”, the Angle is valued fractional should be “whole”, the fractional value and Angle is 0 should be “zero”, so, there should not be “zero”. Of course, these problems can also be handled with the IF function, but this leads to a long formula.

Small editor found that the main problem is in the decimal part of the processing, so we can choose to choose the decimal part of the complex processing to VBA processing, code is as follows:

Function GetDecimal(cell) Dim arrResult() As String arrResult = vba.split (cell, Dim iArr As Integer iArr = UBound(arrResult) 'If iArr = 0 Then GetDecimal = GetDecimal & ElseIf iArr = 1 Then Dim strSmall As String strSmall = arrResult(1) Dim iSmall As Integer Dim strJiao, StrFen As String 'obtain the decimal number iSmall = Len(strSmall)' If iSmall = 1 Then strJiao = getUpperCase ElseIf iSmall = 2 Then strJiao = getUpperCase(Left(strSmall, 1)) strFen = getUpperCase(Right(strSmall, ElsestrJiao = getUpperCase(Left(strSmall, 1)) strFen = getUpperCase(Mid(strSmall, 2) 1)) End If (strFen = "" Or strFen = "zero ") And strJiao =" zero "Then GetDecimal = GetDecimal & ElseIf (strFen = "" Or strFen = "zero ") And strJiao <>" zero "Then GetDecimal = GetDecimal &" yuan "&strjiao &" jiao" ElseIf strFen <> "" And strFen <> "zero" And strJiao = "zero" Then GetDecimal = GetDecimal & "yuan" & "zero" &strfen & ElseIf strFen <> "" And strFen <> "zero" And strJiao <> "zero" Then GetDecimal = GetDecimal & "yuan" & StrJiao & "Angle" &strfen & "cent" End If 'has decimal part but formatting is incorrect Else GetDecimal = GetDecimal & "Data format is wrong" End If End Function' Uppercase number Private Function getUpperCase(STR) As String Dim strWord As String Select Case STR "0": strWord = "0" StrWord = "one" Case "2": strWord = "two" Case "3": strWord = "three" Case "4": strWord = "four" Case "5": strWord = "five" Case "6": Case "7": strWord = "seven" Case "8": strWord = "EIGHT" Case "9": strWord = "nine" Case Else: strWord = str End Select getUpperCase = strWord End FunctionCopy the code

There are also explanations in the code, small editor annotations should be clear, not clear can be discussed with small editor.

Finally combine the integer and decimal parts using the formula “=NUMBERSTRING(TRUNC(B5),2)&GetDecimal(B5)” to get the final result.

Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.

If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.

This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.