Numbers To Text String

idrywall

New Member
Joined
Oct 8, 2002
Messages
3
Found this function on the net and modified it to work.

Function NumberChange(dblNum As Double, Optional bolArt As Variant)
Dim arrArt As Variant
Dim intCounter As Integer, intCts As Integer
Dim strValue As String, strTmp As String, strSuffix As String
On Error Resume Next
If bolArt = 1 Then
If Err = 0 Then
intCts = (dblNum - Fix(dblNum)) * 100
strSuffix = " " & Format(CStr(intCts), " 00") & "/100"
Else
bolArt = 0
End If
End If
On Error GoTo 0
dblNum = Fix(dblNum)
strTmp = Right(CStr(dblNum), 3)
strValue = Part(strTmp)
For intCounter = 1 To 4
strTmp = CStr(dblNum)
Select Case Len(strTmp)
Case Is < 1 + 3 * intCounter
NumberChange = strValue & strSuffix
Exit Function
Case Is < 4 + 3 * intCounter
strTmp = Left(strTmp, Len(strTmp) - intCounter * 3)
Case Else
strTmp = Left(Right(strTmp, 3 + intCounter * 3), 3)
End Select
Select Case intCounter
Case 1: arrArt = Array(" Thousand", " One Thousand")
Case 2: arrArt = Array(" Million", " One Million")
Case 3: arrArt = Array(" Billion", " One Billion")
Case 4: arrArt = Array(" Trillion", " One Trillion")
End Select
If Right(CStr(dblNum), 3) = "000" Then
strValue = Part(strTmp) & arrArt(0)
ElseIf CInt(strTmp) = 1 Then
strValue = arrArt(1) & strValue
Else
strValue = Part(strTmp) & arrArt(0) & strValue
End If
Next intCounter
NumberChange = strValue & strSuffix
End Function

Private Function Part(strPart As String) As String
Dim arrA As Variant, arrB As Variant, arrC As Variant
Dim strTmp As String
arrA = Array("Zero", " One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine")
arrB = Array(" Eleven", " Twelve", " Thirteen", " Fourteen", " Fifteen", " Sixteen", " Seventeen", " Eighteen", " Nineteen")
arrC = Array(" Ten", " Twenty", " Thirty", " Forty", " Fifty", " Sixty", " Seventy", " Eighty", " Ninety")
If Len(strPart) = 1 Then
strTmp = arrA(CInt(Right(strPart, 1)))
ElseIf Right(strPart, 2) = "00" Then
If Left(strPart, 1) = "1" Then
Part = " One Hundred"
Else
Part = arrC(CInt(Left(strPart, 1))) & " Hundred"
End If
Exit Function
ElseIf Mid(strPart, Len(strPart) - 1, 1) = "0" Then
strTmp = arrA(CInt(Right(strPart, 1)))
ElseIf Mid(strPart, Len(strPart) - 1, 1) = "1" Then
If CInt(Right(strPart, 1)) <> 0 Then
strTmp = arrB(CInt(Right(strPart, 2)) - 11)
Else
strTmp = arrC(CInt(Mid(strPart, Len(strPart) - 1, 1)) - 1)
End If
ElseIf CInt(Mid(strPart, Len(strPart) - 1, 1)) > 1 Then
Select Case Right(strPart, 1)
Case "0"
strTmp = arrC(CInt(Mid(strPart, Len(strPart) - 1, 1)) - 1)
Case "1"
strTmp = "One And" & _
arrC(CInt(Mid(strPart, Len(strPart) - 1, 1)) - 1)
Case Else
strTmp = arrC(CInt(Mid(strPart, Len(strPart) - 1, 1)) - 1) & _
arrA(CInt(Right(strPart, 1))) & ""

End Select
End If
If Len(strPart) = 3 Then
Select Case Left(strPart, 1)
Case "0"
Case "1"
strTmp = " One Hundred" & strTmp
Case Else
strTmp = arrA(CInt(Left(strPart, 1))) & " Hundred" & strTmp
End Select
End If
Part = strTmp
End Function
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,144,433
Messages
5,724,317
Members
422,543
Latest member
TimB_13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top