• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Excel Converting 1,278 to "One Thousand Two Hundred and seventy Eight"

I am looking for a ready made VBA script that converts;;

200 to Two Hundred

1,001 to One Thousand and One

1,300,301 to One Million Three Hundred Thousand Three Hundred and one

Can anyone point me in the right direction
0
Patrick O'Dea
Asked:
Patrick O'Dea
1 Solution
 
Rgonzo1971Commented:
Hi,

this page gives a solution on how to transcribe a number into words

with the function SpellNumber(ByVal MyNumber) As String

http://support.microsoft.com/kb/213360

Regards
0
 
Rob HensonIT & Database AssistantCommented:
How high do you need the numbers to go? Will millions suffice or will the numbers go into billions?

I have seen similar questions before so it is worth continuing your search on EE while others contemplate your question.

Thanks
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
A couple of links that I have in the few minutes since first posting:


http://bloople.net/num2text

Several links in this question:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_20149155.html

Thanks
Rob H
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Naresh PatelTraderCommented:
Step 1 Alt+F11 in WB - VBA Window Open.
Step 2 On Left Side Of the VBA Explorer Window. Search For this workbook - Right Click -   Insert Module
Step 3 Copy Code from here & past in Right Side VBA Window - Save - Close VBA Window.
Step 4 Assuming Numerical value in Cell A2 then Formula "=WordNum(A2)".

i don't write Code, my self found on net only.




  Option Explicit
Public Numbers As Variant, Tens As Variant
Sub SetNums()
    Numbers = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
End Sub
Function WordNum(MyNumber As Double) As String
Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
' This macro was written by Chris Mead - www.MeadInKent.co.uk
If Abs(MyNumber) > 999999999 Then
    WordNum = "Value too large"
Exit Function
End If
SetNums
    ' String representation of amount (excl decimals)
NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))
For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
    StrNo = Format(ValNo(n), "000")
    If ValNo(n) > 0 Then
        Temp1 = GetTens(Val(Right(StrNo, 2)))
            If Left(StrNo, 1) <> "0" Then
                Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
                If Temp1 <> "" Then Temp2 = Temp2 & " and "
            Else
        Temp2 = ""
                End If
        If n = 3 Then
            If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
                WordNum = Trim(Temp2 & Temp1)
            End If
    If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
        If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)
            End If
    Next n
    NumStr = Trim(Str(Abs(MyNumber)))
    ' Values after the decimal place
    DecimalPosition = InStr(NumStr, ".")
    Numbers(0) = "Zero"
    If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
    Temp1 = " point"
    For n = DecimalPosition + 1 To Len(NumStr)
    Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
    Next n
    WordNum = WordNum & Temp1
    End If
    If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
    WordNum = "Zero" & WordNum
    End If
    End Function
    Function GetTens(TensNum As Integer) As String
    ' Converts a number from 0 to 99 into text.
    If TensNum <= 19 Then
    GetTens = Numbers(TensNum)
    Else
    Dim MyNo As String
    MyNo = Format(TensNum, "00")
    GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
    End If
End Function

Open in new window


Thanks
0
 
Patrick O'DeaAuthor Commented:
I found itjockey code the best.

Some of the other hyperlinks failed.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now