Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Conas uimhreacha a litriú nó a thiontú go focail Bhéarla in Excel?

Má cheaptar go bhfuil go leor uimhreacha praghais agat, agus anois ní mór duit iad a thiontú nó a litriú go teaghráin téacs. Mar shampla, tiontaigh $ 123.55 go dtí na focail Bhéarla céad fiche trí dollar agus caoga cúig cent. In Excel níl aon áis éifeachtach ann chun an fhadhb seo a réiteach ach an cód VBA fada agus casta a úsáid. San Airteagal seo, tabharfaidh mé roinnt modhanna duit chun déileáil leis.

Litrigh amach nó tiontaigh uimhreacha go focail Bhéarla le cód VBA

Litrigh amach nó tiontaigh uimhreacha go focail Bhéarla le Kutools for Excel


Litrigh amach nó tiontaigh uimhreacha go focail Bhéarla le cód VBA

Is féidir leis an gcód VBA fada seo a leanas cabhrú leat uimhreacha a litriú go teaghráin téacs. Déan mar a leanas:

1. Coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an macra seo a leanas sa Fuinneog an mhodúil.

VBA: Litrigh uimhreacha airgeadra go focail Bhéarla in 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. Ansin sábháil an cód, agus filleadh ar an mbileog oibre, sa Chill B2 in aice láimhe, iontráil an fhoirmle = SpellNumberToEnglish (A1), féach ar an scáileán:

4. Ansin brúigh Iontráil eochair, agus roghnaigh an Cill B2, ansin tarraing an láimhseáil líonta go dtí an raon ina bhfuil an fhoirmle seo de réir mar is gá duit. Agus tá na huimhreacha airgeadra go léir litrithe amach go focail Bhéarla. Féach an pictiúr:

nótaí: Toisc gur foirmlí iad, nuair is gá duit iad a chóipeáil agus a ghreamú, greamaigh mar luachanna iad le do thoil.


Litrigh amach nó tiontaigh uimhreacha go focail Bhéarla le Kutools for Excel

B’fhéidir go gcuirfeadh an cód fada thuas tú ar buile, ná bíodh imní ort, anseo is féidir liom bealach éasca gasta a thabhairt duit chun é a réiteach. Leis an Kutools le haghaidh Excel'S Uimhreacha le Focail feidhm, is féidir leat na huimhreacha a thiontú go teaghráin téacs go tapa agus go caothúil.

Kutools le haghaidh Excel- Cuimsíonn sé níos mó ná 300 uirlis áisiúil le haghaidh Excel. Triail saor in aisce gné iomlán 30 lá, níl aon chárta creidmheasa ag teastáil! Get sé anois

1. Roghnaigh na huimhreacha raon is mian leat a litriú amach, agus cliceáil Kutools > Ábhar > Uimhreacha le Focail.
doc kutools uimhir le focail 1

2. Sa bhosca dialóige popping out, roghnaigh english ó na teangacha. Agus is féidir leat réamhamharc a dhéanamh ar na torthaí ón gceart réamhamharc Pána. Féach an pictiúr:
doc kutools uimhir le focail 2

3. Ansin cliceáil Ok or Cuir iarratas isteach. Agus athraíodh na huimhreacha praghais go léir go teaghráin téacs sa raon bunaidh.
doc kutools uimhir le focail 3

nótaí: Mura dteastaíonn uait ach uimhreacha a thiontú go focail Bhéarla (ní airgeadra), seiceáil le do thoil Gan tiontú go Airgeadra ticbhosca.
doc kutools uimhir le focail 3 doc kutools uimhir le focail 3

An ghné iontach seo de Uimhreacha le Focail is féidir leo uimhreacha airgeadra a aistriú go téacs airgeadra i mBéarla nó i Sínis, mar shampla 32.01 a aistriú go "Tríocha a dó dollar agus aon cent" nó "叁拾 贰元 零 壹 分". Bíodh Triail In Aisce agat!

Demo: Litrigh amach uimhreacha airgeadra go focail Bhéarla


Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

  • Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
  • Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
  • Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
  • Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
  • Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
  • Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
  • Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
  • Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
  • Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.
cluaisín kte 201905

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
bun officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Qaiser Farman · 6 years ago
    Great tool...
    Just one more thing, i dont want it to show this dollar and cents thing as i need only the words?
    Any tool for that. Please??
  • To post as a guest, your comment is unpublished.
    JITESH AGARWAL · 6 years ago
    need help in excell 97-2003
  • To post as a guest, your comment is unpublished.
    saravanaprakash · 6 years ago
    How can i change the 169531535.25 - Sixteen Crores Ninety Five lakhs Thirty One Thousand Five Hundred Thirty Five and Twenty Five Paise
  • To post as a guest, your comment is unpublished.
    Gpaws1968 · 6 years ago
    How can I convert a number value into words 0.04 to say four thousandths, without the Dollars and Cents
  • To post as a guest, your comment is unpublished.
    ron · 6 years ago
    How can i improve this code that the result will be like this "One Hundred Ten Thousand and Twenty Cents (S110,000.20)"
  • To post as a guest, your comment is unpublished.
    TAPAN KUMAR JENA · 7 years ago
    can i change the value 3,25,655 as Three twenty five thousand six hundred fifty five to three lakh twenty five thousand six hundred fifty five. Please help thank you.
  • To post as a guest, your comment is unpublished.
    Carol Khor · 7 years ago
    I need the module code for spell number to change

    1,234,567.89
    into
    RINGGIT MALAYSIA : ONE MILLION TWO HUNDRED THIRTY FOUR THOUSAND FIVE HUNDRED SIXTY SEVEN AND EIGHTY NINE CENTS ONLY

    Thank you very much for your help.
    • To post as a guest, your comment is unpublished.
      X2H · 6 years ago
      Hi Carol, did u manage to get an answer on how to change the spell number to our RM?
  • To post as a guest, your comment is unpublished.
    Aleem · 7 years ago
    thank you really its very usefull
  • To post as a guest, your comment is unpublished.
    pingky · 7 years ago
    So Great! It helpful. But how can I put the word "Only" at the end.How to do. Following is our format.
    ***SINGAPORE DOLLARS SIX THOUSAND EIGHT HUNDRED AND FOURTEEN ONLY***
  • To post as a guest, your comment is unpublished.
    gunaraj · 7 years ago
    thanks for the formula, however, is it possible to get the word "Dollar/Rupees" as a prefix i.e. Rupees One thousand .... and Paisas thirty only.
    please help
  • To post as a guest, your comment is unpublished.
    Dom · 7 years ago
    You have done a great work and is very useful for me, awesome! But here is my question. Since i don't need any dollar and cents, just need the number spelled out, how can I change it??
  • To post as a guest, your comment is unpublished.
    Zakwan · 7 years ago
    [quote name="Firash"]Hi Sir,

    I tried to save the macro as you mentioned above. and it is working fine. but if i close the excel and open a new sheet, it is not working . pls help[/quote]

    I also faced same issue, any help please. thanks
    • To post as a guest, your comment is unpublished.
      Anna · 6 years ago
      Got it! after you paste the program you have to press Alt+Q then save type the file name and select "SAVE AS TYPE" Excel Macro-Enabled Workbook (*.xlsm)..
  • To post as a guest, your comment is unpublished.
    Mikee · 7 years ago
    how can i use same module in access VBA code?
  • To post as a guest, your comment is unpublished.
    Amar Srivastava · 7 years ago
    it's very useful for us thanks it's awesome.
  • To post as a guest, your comment is unpublished.
    Amar Srivastava · 7 years ago
    It's awesome....... this is useful for us.
  • To post as a guest, your comment is unpublished.
    Janine · 7 years ago
    This is awesome!! This is very helpful!
  • To post as a guest, your comment is unpublished.
    Bilash · 7 years ago
    Sophany: Plz save your file as a macro enable mode; hope it'll be work.
  • To post as a guest, your comment is unpublished.
    Sophany · 7 years ago
    I can use this code, but they haven't save and i didn't see what i have done with this code when i reopen the workbook.
  • To post as a guest, your comment is unpublished.
    Bilash · 7 years ago
    I need hepl on same issue "Example is in my general data is 50.576 but when I rounded it off and become 50.58, Words doesn't change/convert according to the new value".
  • To post as a guest, your comment is unpublished.
    roy · 7 years ago
    can anyone help me to spell only whole number not cents. ex. 125,685.25 spells as ONE HUNDRED TWENTY FIVE THOUSAND SIX HUNDRED EIGHTY FIVE & 25/100 ONLY. that's what i want please help me.

    thank you so much
    • To post as a guest, your comment is unpublished.
      Jairo Moreno · 5 years ago
      [quote name="roy"]can anyone help me to spell only whole number not cents. ex. 125,685.25 spells as ONE HUNDRED TWENTY FIVE THOUSAND SIX HUNDRED EIGHTY FIVE & 25/100 ONLY. that's what i want please help me.

      thank you so much[/quote]
      Yes, I was able to make it spell out everything but the cents, and give the cents in a 25/100 Cents format, instead of the "and 25 Cents" as given in the code. The first step is to declare a variable pCents, right where Cents is first declared. The next step is to insert a line of code just after the first time the variable Cents is calculated, and that line of code is "pCents = Left(Mid(pNumber, xDecimal + 1) & "00", 2)" not including the " at beginning and end. Then where Cents is about to be "printed out", replace the ending Cents with pCents and add "/100 Cents" That line should look like this: Cents = " and " & pCents & "/100 Cents" I may explain it better if there is an interest.
      • To post as a guest, your comment is unpublished.
        Staff · 4 years ago
        [quote name="Jairo Moreno"][quote name="roy"]can anyone help me to spell only whole number not cents. ex. 125,685.25 spells as ONE HUNDRED TWENTY FIVE THOUSAND SIX HUNDRED EIGHTY FIVE & 25/100 ONLY. that's what i want please help me.

        thank you so much[/quote]
        Yes, I was able to make it spell out everything but the cents, and give the cents in a 25/100 Cents format, instead of the "and 25 Cents" as given in the code. The first step is to declare a variable pCents, right where Cents is first declared. The next step is to insert a line of code just after the first time the variable Cents is calculated, and that line of code is "pCents = Left(Mid(pNumber, xDecimal + 1) & "00", 2)" not including the " at beginning and end. Then where Cents is about to be "printed out", replace the ending Cents with pCents and add "/100 Cents" That line should look like this: Cents = " and " & pCents & "/100 Cents" I may explain it better if there is an interest.[/quote]


        THANKS A LOT.. IT WORKS LIKE WONDER.. :lol:
        • To post as a guest, your comment is unpublished.
          girlsgeneration · 4 years ago
          [quote name="Staff"][quote name="Jairo Moreno"][quote name="roy"]can anyone help me to spell only whole number not cents. ex. 125,685.25 spells as ONE HUNDRED TWENTY FIVE THOUSAND SIX HUNDRED EIGHTY FIVE & 25/100 ONLY. that's what i want please help me.

          thank you so much[/quote]
          Yes, I was able to make it spell out everything but the cents, and give the cents in a 25/100 Cents format, instead of the "and 25 Cents" as given in the code. The first step is to declare a variable pCents, right where Cents is first declared. The next step is to insert a line of code just after the first time the variable Cents is calculated, and that line of code is "pCents = Left(Mid(pNumber, xDecimal + 1) & "00", 2)" not including the " at beginning and end. Then where Cents is about to be "printed out", replace the ending Cents with pCents and add "/100 Cents" That line should look like this: Cents = " and " & pCents & "/100 Cents" I may explain it better if there is an interest.[/quote]


          THANKS A LOT.. IT WORKS LIKE WONDER.. :lol:[/quote]

          Can you send me your codes? I don't get it. Mine always shows "00 /100 Only" even after changing the decimals and there's a space between the "00 /100". I hope you can help me.
  • To post as a guest, your comment is unpublished.
    SATHISH · 7 years ago
    hai, it was superb. but anyone help me to execute this as it is excluding money(dollar,cent,rupee,paisa,etc.)
  • To post as a guest, your comment is unpublished.
    HASNAIN SHAHID · 7 years ago
    Thanks for this Help
    its working :-)
  • To post as a guest, your comment is unpublished.
    Khalid · 7 years ago
    Fantastic. Thanks to Extend Office
  • To post as a guest, your comment is unpublished.
    vishal shah · 7 years ago
    I want to convert Metric weight from numbers to words.

    eg.
    1. 10.50 Metric Tons (MT) should write as "10 Tons and 500 kgs"
    2. 12.345 MT = Twelve Tons and Three hundred Forty Five Kgs

    Need 3 decimal places atleast and 4 is even better.
    max 9999.9999 is how i want.

    Can someone please help?
    • To post as a guest, your comment is unpublished.
      Islam · 5 years ago
      If you found please email me
      islamrafat@gmail.com
    • To post as a guest, your comment is unpublished.
      Islam · 5 years ago
      Please if you found the way... please email me
      islamrafat@gmail.com
      Thanks
  • To post as a guest, your comment is unpublished.
    Abdul Basit · 7 years ago
    I have conducted a research and could not be able to convert into Pakistan currency system, as we people use this format. 1,00,00,00,00,000.00 but excel use this format 1,000,000,000,000. and the code is in million and trillion formula. I tried to change lack, karor, arab and kharab instead of million, billion and trillion, but it will change only words and the system will remain same. like after change it will show this one. 100,000,000 it should be hundred million, but it will be shown hundred lakhs.If some one can help me i shall be thankfull to you.
  • To post as a guest, your comment is unpublished.
    PAWAN · 7 years ago
    THNX ATONE FOR SUCH TIPS
  • To post as a guest, your comment is unpublished.
    belle · 7 years ago
    its really helpful, really really big thanks :-)
  • To post as a guest, your comment is unpublished.
    Abhishek · 7 years ago
    wow great for the stuff i have been looking for a week. It works great.

    Thanks a lot
  • To post as a guest, your comment is unpublished.
    RICK · 7 years ago
    doesn't work. pop up says class not registered. no option to choose language. error code
  • To post as a guest, your comment is unpublished.
    Hameed · 7 years ago
    Thaks a lot, could you please let me now how can i get indian format(Rupees)
  • To post as a guest, your comment is unpublished.
    Rajendra singh · 7 years ago
    How Can i change amount in english words in indian currency
    • To post as a guest, your comment is unpublished.
      SANTHOSH KONDURU · 5 years ago
      HELLO
      JUS YOU CAN INSTALL SPEL CURR ADDIN YOU CAN CONVERT :) :) :lol:
    • To post as a guest, your comment is unpublished.
      shreesh garg · 7 years ago
      copy the above code language in a word sheet

      replace word "dollar" with "Rupees"
      Then
      Replace word "cents" with "Paisa"

      go back to excel file

      press alt+F11

      insert>>> module >> paste the entire word file till "end function"

      do .....=SpellNumberToEnglish(cellref)

      & you are good to go
      • To post as a guest, your comment is unpublished.
        Firash · 7 years ago
        Hi Sir,

        I tried to save the macro as you mentioned above. and it is working fine. but if i close the excel and open a new sheet, it is not working . pls help
  • To post as a guest, your comment is unpublished.
    Rajan · 7 years ago
    thanks for the formula, however, is it possible to get the word "Dollar/Rupees" as a prefix i.e. Rupees One thousand .... and Paisas thirty only.

    would really appreciate your response.
  • To post as a guest, your comment is unpublished.
    azhar · 7 years ago
    is it possible to get data as 100th after decimal, for example .100 answer shout be as 100 instead of ten
  • To post as a guest, your comment is unpublished.
    ATUL · 7 years ago
    dEAR SIR,


    Its is a very good formula, But I need the words in Indian formats
    PLEASE ADVICE
  • To post as a guest, your comment is unpublished.
    santosh · 7 years ago
    plx make me clear that how can I see it in my country's currency format i.e. Rupees in place of Dollars and Paisa in place of Cents.
  • To post as a guest, your comment is unpublished.
    Mahesh Giri · 7 years ago
    Its is a very good formula, But I need the words in Indian formats
  • To post as a guest, your comment is unpublished.
    jabesh · 7 years ago
    It's a well-working code. Thanks a lot.
  • To post as a guest, your comment is unpublished.
    know it all · 7 years ago
    Problem on rounding off with numbers.

    Example is in my general data is 50.576 but when I rounded it off and become 50.58, Words doesn't change/convert according to the new value.
  • To post as a guest, your comment is unpublished.
    heba · 7 years ago
    Thank you for your valuable information. I need to mention that it's Dirham and not dollar, how can i make it

    Thank you in advance
    • To post as a guest, your comment is unpublished.
      adel · 5 years ago
      I tried this and worked for me...
      go the module, ctrl F to find >>> then in the find put Dollar and then go to replace (in the same popup box) and put Dirham,
      it will work
    • To post as a guest, your comment is unpublished.
      jabesh · 7 years ago
      Please change all the words ~dollar/dollars" and ~cent/cents" in the code into your currency name.
  • To post as a guest, your comment is unpublished.
    i am not getting · 7 years ago
    I am not getting this formula in my Sheet, Can you show me in detail
  • To post as a guest, your comment is unpublished.
    Anees · 8 years ago
    Its Really Cool. And Fount its easy to change the Word " Dollar and Send to Rupees or any other currency we want by Copy pasting the cod to any other new word page and replacing the Same.