Skip i'r prif gynnwys

Sut i sillafu neu drosi rhifau i eiriau Saesneg yn Excel?

Gan dybio bod gennych chi lawer o rifau prisiau, ac nawr mae angen i chi eu trosi neu eu sillafu i dannau testun. Er enghraifft, trosi $123.55 i'r geiriau Saesneg cant dau ddeg tri doler a phum deg pump sent. Yn Excel nid oes cyfleuster effeithiol i ddatrys y broblem hon ond gan ddefnyddio'r cod VBA hir a chymhleth. Yn yr erthygl hon, byddaf yn cyflwyno rhai dulliau ichi ddelio ag ef.

Sillafu neu drosi rhifau i eiriau Saesneg gyda chod VBA

Sillafu neu drosi rhifau i eiriau Saesneg gyda Kutools ar gyfer Excel


Sillafu neu drosi rhifau i eiriau Saesneg gyda chod VBA

Gall y cod VBA hir canlynol eich helpu i sillafu rhifau i dannau testun. Gwnewch fel a ganlyn:

1. Daliwch i lawr y ALT + F11 allweddi, ac mae'n agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

2. Cliciwch Mewnosod > Modiwlau, a gludwch y macro canlynol yn y Ffenestr modiwl.

VBA: Sillafu rhifau arian cyfred i eiriau Saesneg yn Excel

Function SpellNumberToEnglish(ByVal pNumber)
'Updateby20131113
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 & " 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
SpellNumberToEnglish = 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

3. Yna arbedwch y cod, a'i ddychwelyd i'r daflen waith, yn y Cell B2 gyfagos, nodwch y fformiwla = SpellNumberToEnglish (A1), gweler y screenshot:

4. Yna pwyswch Rhowch allwedd, a dewiswch y Cell B2, yna llusgwch y ddolen llenwi i'r ystod sy'n cynnwys y fformiwla hon yn ôl yr angen. Ac mae'r holl rifau arian cyfred wedi'u sillafu allan i eiriau Saesneg. Gweler y screenshot:

Nodyn: Gan eu bod yn fformiwlâu, pan fydd angen i chi eu copïo a'u pastio, pastiwch fel gwerthoedd.


Sillafu neu drosi rhifau i eiriau Saesneg gyda Kutools ar gyfer Excel

Efallai y gall y cod hir uchod wneud i chi fod yn wallgof, peidiwch â phoeni, yma gallaf roi ffordd hawdd a chyflym i chi i'w ddatrys. Efo'r Kutools ar gyfer Excel'S Rhifau i Eiriau swyddogaeth, gallwch drosi'r rhifau yn dannau testun yn gyflym ac yn gyfleus.

Kutools ar gyfer Excel- Yn cynnwys mwy na 300 o offer defnyddiol ar gyfer Excel. Treial llawn am ddim nodwedd 30 diwrnod, nid oes angen cerdyn credyd! Get It Now

1. Dewiswch y rhifau amrediad rydych chi am eu sillafu allan, a chlicio Kutools > Cynnwys > Rhifau i Eiriau.
doc kutools rhif i eiriau 1

2. Yn y blwch deialog popio allan, dewiswch Saesneg oddi wrth y Ieithoedd. A gallwch chi ragolwg o'r canlyniadau o'r dde Rhagolwg Pane. Gweler y screenshot:
doc kutools rhif i eiriau 2

3. Yna cliciwch Ok or Gwneud cais. Ac mae'r holl rifau prisiau wedi'u trosi i dannau testun yn yr ystod wreiddiol.
doc kutools rhif i eiriau 3

Nodyn: Os ydych chi am drosi rhifau yn eiriau Saesneg (nid arian cyfred), gwiriwch Heb ei drosi i Arian Cyfred checkbox.
doc kutools rhif i eiriau 3 doc kutools rhif i eiriau 3

Y nodwedd wych hon o Rhifau i Eiriau yn gallu cyfieithu rhifau arian cyfred yn hawdd i destun arian cyfred yn Saesneg neu Tsieinëeg, megis cyfieithu 32.01 i "Tri deg dau o ddoleri ac un cant" neu "叁拾 贰元 零 壹 分". Cael Treial Am Ddim!

Demo: Sillafu rhifau arian cyfred i eiriau Saesneg

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog    VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr    Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd    Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad


Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (112)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi! How would I go about adding hyphens ( - ) between compound numbers, so instead of it spelling 55 as Fifty Five it would spell Fifty-Five. Simply adding hyphens to all "tens" obviously doesn't work since that would mean tat 20,000,000 would be spelled as "Twenty- Million". Thanks to whoever can solve this!
This comment was minimized by the moderator on the site
Hi! How would I go about adding hyphens ( - ) between compound numbers, so instead of it spelling 55 as Fifty Five it would spell Fifty-Five. Simply adding hyphens to all "tens" obviously doesn't work since that would mean tat 20,000,000 would be spelled as "Twenty- Million". Thanks to whoever can solve this!
This comment was minimized by the moderator on the site
not suitable for indian currency allways show in doller
This comment was minimized by the moderator on the site
Hi prem,
At present, this Numbers to Words feature enables to convert numbers to Chinese Yuan and US Dollars.
This comment was minimized by the moderator on the site
How can i get actuall figure without "no cents/ no dollars", if it's not needed. Please Email me in shown Mail ID.
This comment was minimized by the moderator on the site
HAVE U GOT ANY SOLUTION FOR THIS
This comment was minimized by the moderator on the site
just delete the no cents on line 46 and no dollars on line 38, leave it "" only
This comment was minimized by the moderator on the site
I was unable to do it, I don't know what happened to it, first of all it was OK, but only for one time then it shows this sign #NAME? can any one help me in this regards.
This comment was minimized by the moderator on the site
Save it as Excel Macro Enable Workbook. and each time you will open the file, you'll see a Security Warning prompt (usually on top of Formula Bar) you have to click the Option button and tick the "Enable this content". This would solve your problem. Hope it would help
This comment was minimized by the moderator on the site
Please try one more time
This comment was minimized by the moderator on the site
everytime i close the file.. the complete program is lost.. how can i save this for all my excel files
This comment was minimized by the moderator on the site
thanks for the excellent job, I tried it , great , its working , greetings from Sri Lanka
This comment was minimized by the moderator on the site
We thank you for this guideline it is most helpful, but how can i change the currency from Dollars to Rwandan Francs?

Thanks
This comment was minimized by the moderator on the site
search the below texts in the vba code and replace eg : search 1 >> Search for "Dollars" Replace with "Rwandan Francs"
search 2 >> Search for "Dollar" Replace with "Rwandan Franc"
This comment was minimized by the moderator on the site
I have followed the guidelines and managed to do it perfectly well for the first time. I have been designing templates and it has worked for me. Thank you so much.
This comment was minimized by the moderator on the site
i wan to change this eg:( one hundred thousand rupees only ), pls explain me how to get that word "only"
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations