I cookie ci aiutano i nostri servizi. Utilizzando i nostri servizi, l'utente accetta di utilizzare i cookie.
Suggerimento: le altre lingue sono tradotte da Google. Puoi visitare il English versione di questo link.
Log In
x
or
x
x
Registrati
x

or

Come compitare o convertire i numeri in parole inglesi in Excel?

Supponendo che tu abbia molti numeri di quantità di prezzo, e ora devi convertirli o compitarli in stringhe di testo. Ad esempio, convertire 123.55 alle parole inglesi centoventitre dollari e cinquantacinque centesimi. In Excel non esistono funzionalità efficaci per risolvere questo problema ma utilizzando il codice VBA lungo e complesso. In questo articolo, ti presenterò alcuni metodi per affrontarlo.

Spiegare o convertire i numeri in parole inglesi con codice VBA

Spiegare o convertire i numeri in parole inglesi con Kutools per Excelbuona idea3


Spiegare o convertire i numeri in parole inglesi con codice VBA


Il seguente codice VBA lungo può aiutarti a compitare i numeri alle stringhe di testo. Fai come segue:

1. Tenere premuto il tasto ALT + F11 chiavi, e apre il Microsoft Visual Basic, Applications Edition finestra.

2. Clic inserire > Moduloe incollare la seguente macro in Modulofinestra.

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. Quindi salvare il codice e tornare al foglio di lavoro, nella cella adiacente E1, immettere la formula = SpellNumberToEnglish (D1), vedi screenshot:

doc-spell-out-numbers1

4. Quindi premere entrare chiave e selezionare la cella E1, quindi trascinare il quadratino di riempimento sull'intervallo che contiene questa formula come necessario. E tutti i numeri sono stati espressi in parole inglesi. Vedi screenshot:

doc-spell-out-numbers2

Note: Poiché sono formule, quando è necessario copiarle e incollarle, incollare come valori.


Spiegare o convertire i numeri in parole inglesi con Kutools per Excel

Forse il codice lungo sopra può farti arrabbiare, per favore non ti preoccupare, qui posso darti un modo facile e veloce per risolverlo.

Con il Kutools for Excel'S Scrivi i numeri funzione, è possibile convertire rapidamente e comodamente i numeri in stringhe di testo.

Kutools for Excel, con oltre 120 funzioni a portata di mano, facilita il lavoro.

Dopo l'installazione Kutools per Excel, si prega di fare come di seguito:(Scarica gratis Kutools per Excel ora!)

1. Seleziona i numeri dell'intervallo che vuoi precisare.

2. Clic Kutools > Confezione > Scrivi i numeri. Vedi screenshot:

doc spelling il numero alla parola 1

3. Nel Scrivi i numeri finestra di dialogo, scegliere Inglese dal Le lingue. E puoi vedere in anteprima i risultati da destra anteprima Pane. Vedi screenshot:

doc compitare numeri 02

4. Quindi fare clic Ok or Applicare. E tutti i numeri di importo del prezzo sono stati convertiti in stringhe di testo nell'intervallo originale.

doc-spell-out-numbers5-2doc-spell-out-numbers6

Note: Se hai bisogno delle parole relative al prezzo cinese, fai clic Cinese opzione dal Le lingue.

Fai clic su Spelling Out Numbers per saperne di più su questa funzione.



Strumenti di produttività consigliati

Office Tab

stella d&#39;oro1 Porta pratiche schede ad Excel e ad altri software Office, proprio come Chrome, Firefox e il nuovo Internet Explorer.

Kutools for Excel

stella d&#39;oro1 Stupefacente! Aumenta la tua produttività in 5 minuti. Non servono abilità speciali, risparmiate due ore al giorno!

stella d&#39;oro1 300 Nuove funzionalità per Excel, rendono Excel molto semplice e potente:

  • Unisci cella / righe / colonne senza perdere dati.
  • Combina e consolida più fogli e cartelle di lavoro.
  • Confronta intervalli, copia intervalli multipli, conversione testo in data, unità e conversione valuta.
  • Contare in base a colori, totali parziali cercapersone, ordinamento avanzato e filtro super,
  • Altro Seleziona / Inserisci / Elimina / Testo / Formato / Link / Commento / Cartelle di lavoro / Fogli di lavoro Strumenti ...

Schermata di Kutools per Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    roy · 4 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 · 2 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 · 1 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 · 1 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 · 4 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 · 4 years ago
    Thanks for this Help
    its working :-)
  • To post as a guest, your comment is unpublished.
    Khalid · 4 years ago
    Fantastic. Thanks to Extend Office
  • To post as a guest, your comment is unpublished.
    vishal shah · 4 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 · 2 years ago
      If you found please email me
      islamrafat@gmail.com
    • To post as a guest, your comment is unpublished.
      Islam · 2 years ago
      Please if you found the way... please email me
      islamrafat@gmail.com
      Thanks