Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change figure to words
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change figure to words
http://xldynamic.com/source/xld.xlFAQ0004.html
-- Kind regards, Niek Otten "rajeev kejriwal" <rajeev wrote in message ... |i am regularly required to write figures in words. it is a tedious job to job | to write figures (like 1500) to words (like one thousand five hundred). | | there must be some application/ system in excel fo rthis. Please help. write | to me at |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change figure to words
Function InLetters(NumberToLeters)
x = Int(Round(NumberToLeters, 2)) DES = Right(Application.WorksheetFunction.Text(NumberToL eters, "0.00"), 2) If DES 0 Then des1 = " AND " & Right(DES, 2) & "/100 " End If FirstTwoDigits = Right(x, 2) Onetonine = Array("", " ONE ", " TWO ", " THREE ", " FOUR ", " FIVE ", " SIX ", " SEVEN ", " EIGHT ", " NINE ", " TEN ", _ " ELEVEN ", " TWELVE ", " THIRTEEN ", " FOURTEEN ", " FIFTEEN ", " SIXTEEN ", " SEVENTEEN ", " EIGHTEEN ", " NINETEEN ", " TWENTY ", _ " TWENTY ONE", " TWENTY TWO ", " TWENTY THREE ", " TWENTY FOUR ", " TWENTY FIVE ", " TWENTY SIX ", " TWENTY SEVEN ", " TWENTY EIGHT ", " TWENTY NINE ", " THIRTY ", _ " THIRTY ONE ", " THIRTY TWO ", " THIRTY THREE ", " THIRTY FOUR ", " THIRTY FIVE ", " THIRTY SIX ", " THIRTY SEVEN ", " THIRTY EIGHT ", " THIRTY NINE ", " FORTY ", _ " FORTY ONE ", " FORTY TWO ", " FORTY THREE ", " FORTY FOUR ", " FORTY FIVE ", " FORTY SIX ", " FORTY SEVEN ", " FORTY EIGHT ", " FORTY NINE ", " FIFTY ", _ " FIFTY ONE ", " FIFTY TWO ", " FIFTY THREE ", " FIFTY FOUR ", " FIFTY FIVE ", " FIFTY SIX ", " FIFTY SEVEN ", " FIFTY EIGHT ", " FIFTY NINE ", " SIXTY ", _ " SIXTY ONE ", " SIXTY TWO ", " SIXTY THREE ", " SIXTY FOUR ", " SIXTY FIVE ", " SIXTY SIX ", " SIXTY SEVEN ", " SIXTY EIGHT ", " SIXTY NINE ", " SEVENTY ", _ " SEVENTY ONE ", " SEVENTY TWO ", " SEVENTY THREE ", " SEVENTY FOUR ", " SEVENTY FIVE ", " SEVENTY SIX ", " SEVENTY SEVEN ", " SEVENTY EIGHT ", " SEVENTY NINE ", " EIGHTY ", _ " EIGHTY ONE ", " EIGHTY TWO ", " EIGHTY THREE ", " EIGHTY FOUR ", " EIGHTY FIVE ", " EIGHTY SIX ", " EIGHTY SEVEN ", " EIGHTY EIGHT ", " EIGHTY NINE ", " NINETY ", _ " NINETY ONE ", " NINETY TWO ", " NINETY THREE ", " NINETY FOUR ", " NINETY FIVE ", " NINETY SIX ", " NINETY SEVEN ", " NINETY EIGHT ", " NINETY NINE ") FirstTwoDigits1 = Onetonine(FirstTwoDigits) '--------------- ThirdDigit = Int(Right(x, 3) / 100) If ThirdDigit 0 Then ThirdDigit1 = Onetonine(ThirdDigit) & "HUNDRED" End If '---------------- FirstSixDigits = Right(x, 6) NoOfThosands = Int(FirstSixDigits / 1000) FourthAndFifthDigit = Right(NoOfThosands, 2) If FourthAndFifthDigit 0 Then FourthAndFifthDigit1 = Onetonine(FourthAndFifthDigit) & " THOUSAND " End If SixthDigit = Int(FirstSixDigits / 100000) If SixthDigit 0 Then SixthDigit1 = Onetonine(SixthDigit) & " HUNDRED " End If If FourthAndFifthDigit = 0 And SixthDigit 0 Then SixthDigit2 = SixthDigit1 & "THOUSAND" Else SixthDigit2 = SixthDigit1 End If '--------- SeventhAndEighth = Right(x, 8) NoOfMillions = Int(SeventhAndEighth / 1000000) If NoOfMillions 0 Then NoOfMillions1 = Onetonine(NoOfMillions) & "MILLION" End If If NumberToLeters 0 And NumberToLeters < 100000000 Then InLetters = Application.WorksheetFunction.Proper(Trim(NoOfMill ions1 & SixthDigit2 & FourthAndFifthDigit1 & ThirdDigit1 & FirstTwoDigits1 & des1 & " ONLY ")) End If If NumberToLeters = 100000000 Then InLetters = "HUNDRED MILLION ONLY " End If End Function "Niek Otten" wrote: http://xldynamic.com/source/xld.xlFAQ0004.html -- Kind regards, Niek Otten "rajeev kejriwal" <rajeev wrote in message ... |i am regularly required to write figures in words. it is a tedious job to job | to write figures (like 1500) to words (like one thousand five hundred). | | there must be some application/ system in excel fo rthis. Please help. write | to me at |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change figure to words
http://www.meadinkent.co.uk/xlnumberstext.htm heres a good site -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=536405 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to convert a figure in words? | Excel Discussion (Misc queries) | |||
Is their any Function to convert no. in figure to in words | Excel Worksheet Functions | |||
Change over time fornula | Excel Worksheet Functions | |||
how to change all the words of one type(Gunsuh type)to another | Excel Discussion (Misc queries) | |||
change the colour of text in cell for minus figure | Excel Worksheet Functions |