[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

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

Posted on 2014-04-01
Medium Priority
457 Views
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
Question by:Patrick O'Dea
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 53

Expert Comment

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 33

Expert Comment

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 33

Expert Comment

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

LVL 8

Accepted Solution

Naresh Patel earned 2000 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
``````

Thanks
0

Author Closing Comment

ID: 39990203
I found itjockey code the best.

Some of the other hyperlinks failed.
0

## Featured Post

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month14 days, 15 hours left to enroll

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

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