SpellNumber

[mutuku]

Hi Mr. Excel.

How can i use SpellNumber without Dollar. example i write 1600000-One Million, Six Hundred Thousand,Only

 

[Special-K99]

Use this function instead (copied from another site, dunno if Im allowed to say which)

Function NumberstoWords(ByVal pNumber)
'Updateby20140220
Dim Dollars
arr = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
pNumber = Trim(Str(pNumber))
xDecimal = InStr(pNumber, ".")
If xDecimal > 0 Then
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
NumberstoWords = Dollars
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

 

[mutuku]

This code is showing error #name ?

use this function instead (copied from another site, dunno if im allowed to say which)

function numberstowords(byval pnumber)
'updateby20140220
dim dollars
arr = array("", "", " thousand ", " million ", " billion ", " trillion ")
pnumber = trim(str(pnumber))
xdecimal = instr(pnumber, ".")
if xdecimal > 0 then
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
numberstowords = dollars
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

 

[jproffer]

Did you add the code into a module in your workbook? And did you notice the function has a different name? This one would be like

=numberstowords(A1)

 

[mutuku]

Did you add the code into a module in your workbook? And did you notice the function has a different name? This one would be like

=numberstowords(A1)

Yes, it's working
1140077- One Million One Hundred Forty Thousand Seventy Seven

Is there a way i can modify the code to write-One Million One Hundred Forty Thousand & Seventy Seven Shillings?

<colgroup><col width="92"><col span="2" width="64"><col width="107"><col width="64"><col width="65"></colgroup><tbody>
</tbody>

<colgroup><col width="92"></colgroup><tbody>
</tbody>