Spellnumber

Save Share

This query is : Resolved

Bhanu Pratap Singh (Querist)
Follow

09 August 2022 If we enter a number it will convert into individual strings and display in same order.

Ex: If I enter 564 then the output should be FIVE SIX FOUR.

So can anyone modify this code to get this result.

Function SpellNumber(amt As Variant) As Variant

Dim FIGURE As Variant
Dim LENFIG As Integer
Dim i As Integer
Dim WORDs(19) As String
Dim tens(9) As String
WORDs(1) = "ONE"
WORDs(2) = "TWO"
WORDs(3) = "THREE"
WORDs(4) = "FOUR"
WORDs(5) = "FIVE"
WORDs(6) = "SIX"
WORDs(7) = "SEVEN"
WORDs(8) = "EIGHT"
WORDs(9) = "NINE"
WORDs(10) = "TEN"
WORDs(11) = "ELEVEN"
WORDs(12) = "TWELVE"
WORDs(13) = "THIRTEEN"
WORDs(14) = "FOURTEEN"
WORDs(15) = "FIFTEEN"
WORDs(16) = "SIXTEEN"
WORDs(17) = "SEVENTEEN"
WORDs(18) = "EIGHTEEN"
WORDs(19) = "NINETEEN"
tens(2) = "TWENTY "
tens(3) = "THIRTY "
tens(4) = "FOURTY "
tens(5) = "FIFTY "
tens(6) = "SIXTY "
tens(7) = "SEVENTY "
tens(8) = "EIGHTY "
tens(9) = "NINETY "
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
FIGLEN = Len(FIGURE)
If FIGLEN 1 Then
ElseIf Val(Left(FIGURE, 9)) = 1 Then
End If
For i = 1 To 3
If Val(Left(FIGURE, 2)) 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " CRORE "
ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " LAKH "
ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
SpellNumber = SpellNumber & " THOUSAND "
End If
FIGURE = Mid(FIGURE, 3)
Next i
If Val(Left(FIGURE, 1)) > 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 1))) + " HUNDRED "
End If
FIGURE = Mid(FIGURE, 2)
If Val(Left(FIGURE, 2)) 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
FIGURE = Mid(FIGURE, 4)
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " PAISE "
If Val(Left(FIGURE, 2)) 0 Then
SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))
SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
If Val(FIGURE) > 0 Then
SpellNumber = SpellNumber & " ONLY "
End If
End Function

Aashok Kumar Sharma

Aashok Kumar Sharma

(Expert)
Follow

25 July 2025 To achieve your specific requirement — **converting a number like `564` to the string "FIVE SIX FOUR"** — the current code is unnecessarily complex. You don’t need logic for crores, lakhs, paise, etc. You simply need to map each digit individually.

Here’s a simplified version of the `SpellNumber` function in **VBA** that does exactly what you're asking:

---

### ✅ Modified VBA Code:

```vba
Function SpellNumber(ByVal amt As Variant) As String
Dim digits As String
Dim result As String
Dim i As Integer
Dim Words(9) As String

' Define the word mapping for digits 0-9
Words(0) = "ZERO"
Words(1) = "ONE"
Words(2) = "TWO"
Words(3) = "THREE"
Words(4) = "FOUR"
Words(5) = "FIVE"
Words(6) = "SIX"
Words(7) = "SEVEN"
Words(8) = "EIGHT"
Words(9) = "NINE"

' Convert input to string
digits = CStr(amt)

' Loop through each character and convert to word
For i = 1 To Len(digits)
If Mid(digits, i, 1) >= "0" And Mid(digits, i, 1) <= "9" Then
result = result & Words(Val(Mid(digits, i, 1))) & " "
End If
Next i

' Trim trailing space
SpellNumber = Trim(result)
End Function
```

---

### 🔄 Example Usage:

* Input in Excel: `=SpellNumber(564)`
* Output: `FIVE SIX FOUR`

---

### 📝 Notes:

* Handles digits only. You can extend it to ignore dots (`.`) if you input decimal numbers.
* Treats input as a sequence of digits, not as an actual number.

If you’d like this function to handle decimals (e.g. `123.45` → "ONE TWO THREE POINT FOUR FIVE"), I can provide an extended version. Let me know!

You need to be the querist or approved CAclub expert to take part in this query .
Click here to login now