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

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

Rating

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

使用自訂函數，在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

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

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

年紀越大壓力越大

以前我去婚友社時

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

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

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

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

行動吧!

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

或搜尋"紅娘李姐"

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