Solved

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

Posted on 2014-04-01
6
419 Views
Last Modified: 2014-04-09
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
Comment
Question by:Patrick O'Dea
6 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39969816
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39969821
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39969842
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 9

Expert Comment

by:rfportilla
ID: 39969839
0
 
LVL 8

Accepted Solution

by:
itjockey earned 500 total points
ID: 39970394
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
 

Author Closing Comment

by:Patrick O'Dea
ID: 39990203
I found itjockey code the best.

Some of the other hyperlinks failed.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now