前些天有人問過轉中文大寫的，轉英文金額有點複雜，我們一起嘗試下，先看動态效果圖：

轉英文金額相對于轉中文金額難度要大，得自定義一個函數

1. 我們首先新建一個宏函數“數字轉英文”，左下角右鍵“查看代碼”，在彈出的窗口中插入一個新模闆，将代碼粘貼上去。

2. 之後在單元格内插入函數'fx'----'用戶定義'-----'數字轉英文'-----'确定'!

3. 最後在需要轉換英文的單元格填充好公式就可以了！

具體代碼如下：

Function 數字轉英文(ByVal pNumber)

Dim Dollars, Cents

arr = Array('', '', ' Thousand ', ' Million ', ' Billion ', ' Trillion ')

pNumber = Trim(Str(pNumber))

xDecimal = InStr(pNumber, '.')

If xDecimal > 0 Then

Cents = GetTens(Left(Mid(pNumber, xDecimal 1) & '00', 2))

pNumber = Trim(Left(pNumber, xDecimal - 1))

End If

xIndex = 1

Do While pNumber <> ''

xHundred = ''

xValue = Right(pNumber, 3)

If Val(xValue) <> 0 Then

xValue = Right('000' & xValue, 3)

If Mid(xValue, 1, 1) <> '0' Then

xHundred = GetDigit(Mid(xValue, 1, 1)) & ' Hundred '

End If

If Mid(xValue, 2, 1) <> '0' Then

xHundred = xHundred & GetTens(Mid(xValue, 2))

Else

xHundred = xHundred & GetDigit(Mid(xValue, 3))

End If

End If

If xHundred <> '' Then

Dollars = xHundred & arr(xIndex) & Dollars

End If

If Len(pNumber) > 3 Then

pNumber = Left(pNumber, Len(pNumber) - 3)

Else

pNumber = ''

End If

xIndex = xIndex 1

Loop

Select Case Dollars

Case ''

Dollars = 'No Dollars'

Case 'One'

Dollars = 'One Dollar'

Case Else

Dollars = Dollars

End Select

Select Case Cents

Case ''

Cents = ' and No Cents'

Case 'One'

Cents = ' and One Cent'

Case Else

Cents = ' and ' & 'Cents ' & Cents

End Select

數字轉英文 = 'US Dollar ' & Dollars & Cents

End Function

Function GetTens(pTens)

Dim Result As String

Result = ''

If Val(Left(pTens, 1)) = 1 Then

Select Case Val(pTens)

Case 10: Result = 'Ten'

Case 11: Result = 'Eleven'

Case 12: Result = 'Twelve'

Case 13: Result = 'Thirteen'

Case 14: Result = 'Fourteen'

Case 15: Result = 'Fifteen'

Case 16: Result = 'Sixteen'

Case 17: Result = 'Seventeen'

Case 18: Result = 'Eighteen'

Case 19: Result = 'Nineteen'

Case Else

End Select

Else

Select Case Val(Left(pTens, 1))

Case 2: Result = 'Twenty '

Case 3: Result = 'Thirty '

Case 4: Result = 'Forty '

Case 5: Result = 'Fifty '

Case 6: Result = 'Sixty '

Case 7: Result = 'Seventy '

Case 8: Result = 'Eighty '

Case 9: Result = 'Ninety '

Case Else

End Select

Result = Result & GetDigit(Right(pTens, 1))

End If

GetTens = Result

End Function

Function GetDigit(pDigit)

Select Case Val(pDigit)

Case 1: GetDigit = 'One'

Case 2: GetDigit = 'Two'

Case 3: GetDigit = 'Three'

Case 4: GetDigit = 'Four'

Case 5: GetDigit = 'Five'

Case 6: GetDigit = 'Six'

Case 7: GetDigit = 'Seven'

Case 8: GetDigit = 'Eight'

Case 9: GetDigit = 'Nine'

Case Else: GetDigit = ''

End Select

End Function

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!