• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • 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
Patrick O'Dea
Patrick O'Dea
1 Solution

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

with the function SpellNumber(ByVal MyNumber) As String


Rob HensonFinance AnalystCommented:
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.

Rob H
Rob HensonFinance AnalystCommented:
A couple of links that I have in the few minutes since first posting:


Several links in this question:


Rob H
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
    ' 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 "
        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)
    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

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

Some of the other hyperlinks failed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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