在EXCEL 中如何將數字轉為英文大寫

各位大大:

我想在 EXCEL 中把數字金額轉為英文,如下例: 請幫幫忙!!

35562.3 轉為:THIRTY-FIVE THOUSAND FIVE HUNDRED SIXTY-TWO DOLLARS AND THIRTY CENTS

4 Answers

Rating
  • 2 decades ago
    Favorite Answer

    用現成函數組合公式將會很長,使用不便,故

    使用自訂函數,在VBA編輯畫面插入一模組,將下列兩個自訂函數程式貼上,即可在工作表中使用。

    使用方法1:假設數值資料在 A1,則在任一儲存格輸入公式

    =EE(A1)  則顯示為英文

    使用方法2:

    =EE(35562.3)

    '~以下為自訂函數~

    Function EE(N As Double) As String

     Dim SN As String

     Dim DS(6) As String

     DS(1) = " HUNDRED "

     DS(2) = " THOUSAND "

     DS(3) = " MILLION "

     If N - Int(N) >= 0.02 Then DS(0) = " CENTS" Else DS(0) = " CENT"

     If N >= 2 Then DS(4) = " DOLLARS" Else DS(4) = " DOLLAR"

     DS(5) = " AND "

     SN = Format(N, "000000000.00")

     A1 = EG(Val(Left(SN, 1)))

     If A1 <> "" Then B1 = A1 + DS(1) Else B1 = A1

     A1 = EG(Val(Mid(SN, 2, 2)))

     If A1 <> "" Then B1 = B1 + A1

     If B1 <> "" Then B1 = B1 + DS(3)

     A2 = EG(Val(Mid(SN, 4, 1)))

     If A2 <> "" Then B2 = A2 + DS(1) Else B2 = A2

     A2 = EG(Val(Mid(SN, 5, 2)))

     If A2 <> "" Then B2 = B2 + A2

     If B2 <> "" Then B2 = B2 + DS(2)

     A3 = EG(Val(Mid(SN, 7, 1)))

     If A3 <> "" Then B3 = A3 + DS(1) Else B3 = A3

     A3 = EG(Val(Mid(SN, 8, 2)))

     If A3 <> "" Then B3 = B3 + A3

     If B1 + B2 + B3 <> "" Then B3 = B3 + DS(4)

     A4 = EG(Val(Mid(SN, 11, 2)))

     If A4 <> "" Then B4 = A4 + DS(0) Else B4 = A4

     B5 = B1 + B2 + B3

     If B5 <> "" And B4 <> "" Then B4 = DS(5) + B4

     EE = B5 + B4

    End Function

    Function EG(v As Integer) As String

     Dim en(19) As String

     Dim tn(9) As String

     Dim V1 As Integer

     Dim V2 As Integer

     en(0) = ""

     en(1) = "ONE"

     en(2) = "TWO"

     en(3) = "THREE"

     en(4) = "FOUR"

     en(5) = "FIVE"

     en(6) = "SIX"

     en(7) = "SEVEN"

     en(8) = "EIGHT"

     en(9) = "NINE"

     en(10) = "TEN"

     en(11) = "ELEVEN"

     en(12) = "TWELVE"

     en(13) = "THIRTEEN"

     en(14) = "FOURTEEN"

     en(15) = "FIFTEEN"

     en(16) = "SIXTEEN"

     en(17) = "SEVENTEEN"

     en(18) = "EIGHTEEN"

     en(19) = "NINETEEN"

     tn(0) = ""

     tn(1) = ""

     tn(2) = "TWENTY"

     tn(3) = "THIRTY"

     tn(4) = "FORTY"

     tn(5) = "FIFTY"

     tn(6) = "SIXTY"

     tn(7) = "SEVENTY"

     tn(8) = "EIGHTY"

     tn(9) = "NINETY"

     V1 = v Mod 100

     If V1 <= 19 Then

      EG = en(V1)

     Else

      EG = tn(Int(V1 / 10))

      If V1 Mod 10 Then EG = EG + "-" + en(V1 Mod 10)

     End If

    End Function

    Source(s): TEST
  • 7 years ago

    如果你很確定自己很想結婚

    婚友社的確是一個有效率的管道

    年紀越大壓力越大

    以前我去婚友社時

    年輕的女生機會總是比較多

    時間總是不站在女生這邊的

    所以如果你想婚的念頭已經確定

    就去找一家評價好一點的婚友社!

    行動吧!

    這是我之前參加的婚友社我覺得不錯

    或搜尋"紅娘李姐"

    不過還是建議你自己去諮詢過看是否適合你!

  • 1 decade ago

    可以把解決問題的步驟[圖示]出來寄給我嗎?拜託

Still have questions? Get your answers by asking now.