Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to change $1,000.00 into One thousand dollar
I have Excel 2007 and need help in how to change "$1,100.00" into "One
thousand one hundred dollars" or something close. Need it spelled out not numeric. Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to change $1,000.00 into One thousand dollar
Several solutions he
http://xldynamic.com/source/xld.xlFAQ0004.html -- Kind regards, Niek Otten Microsoft MVP - Excel "Leiprecht" wrote in message ... I have Excel 2007 and need help in how to change "$1,100.00" into "One thousand one hundred dollars" or something close. Need it spelled out not numeric. Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to change $1,000.00 into One thousand dollar
Does this do what you want?
Function NumberToText(Num As Variant, Optional vCurName As Variant, Optional vCent As Variant) As Variant Dim TMBT As Variant Dim sNum As String, sDec As String, sHun As String, IC As Integer Dim Result As String, sCurName As String, sCent As String If Application.IsNumber(Num) = False Then NumberToText = CVErr(xlValue) Exit Function End If If IsMissing(vCurName) Then sCurName = "" Else sCurName = Trim(CStr(vCurName)) End If If IsMissing(vCent) Then sCent = "" Else sCent = Trim(CStr(vCent)) End If TMBT = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion") If IsMissing(sCent) Or IsNull(sCent) Then sNum = Format(Application.Round(Num, 0), "0") Else sNum = Format(Application.Round(Num, 2), "0.00") sDec = Right(sNum, 2) sNum = Left(sNum, Len(sNum) - 3) If CInt(sDec) < 0 Then sDec = "and " & Trim(HundredsToText(CVar(sDec)) & " " & sCent) Else sDec = "" End If End If IC = 0 While Len(sNum) 0 sHun = Right(sNum, 3) sNum = Left(sNum, Application.Max(Len(sNum) - 3, 0)) If CInt(sHun) < 0 Then Result = Trim(Trim(HundredsToText(CVar(sHun)) & " " & TMBT(IC)) & " " & Result) End If IC = IC + 1 Wend Result = Trim(Result & " " & sCurName) Result = Trim(Result & " " & sDec) NumberToText = Result End Function Function HundredsToText(Num As Integer) As String Dim Units As Variant, Teens As Variant, Tens As Variant Dim I As Integer, IUnit As Integer, ITen As Integer, IHundred As Integer Dim Result As String Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine") Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen") Tens = Array("", "", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety") Result = "" IUnit = Num Mod 10 I = Int(Num / 10) ITen = I Mod 10 IHundred = Int(I / 10) If IHundred 0 Then Result = Units(IHundred) & " Hundred" End If If ITen = 1 Then Result = Result & " " & Teens(IUnit) Else If ITen 1 Then Result = Trim(Result & " " & Tens(ITen) & " " & Units(IUnit)) Else Result = Trim(Result & " " & Units(IUnit)) End If End If HundredsToText = Result End Function You use this like this in a cell formula: =NumberToText(A1,"Dollars","Cents") "Leiprecht" wrote: I have Excel 2007 and need help in how to change "$1,100.00" into "One thousand one hundred dollars" or something close. Need it spelled out not numeric. Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i round a dollar amount to the nearest thousand? | Excel Worksheet Functions | |||
in excel, want data to change auto from 1000 to one thousand | Excel Discussion (Misc queries) | |||
I want to convert 10,000 to "Ten Thousand" using Excel Function | Excel Worksheet Functions | |||
How do i change the currency from dollar to GB pounds | New Users to Excel | |||
WHATS AN EASY FUNCTION TO USE TO ROUND TO THE NEAREST THOUSAND? | Excel Worksheet Functions |