Browse » Home
Saturday, August 11, 2012
How to Convert Numbers Into Letters in MS Excel
1. Open your spreadsheet in Microsoft Excel.
2. Click 'Tools' in the top menu bar, select 'Macro,' and choose the 'Visual Basic Editor' option. If you use Excel 2007 or later, click the 'Developer' tab and click the 'Visual Basic' button instead.
3. Click 'Insert' in the top menu bar of the Visual Basic Editor and select 'Module.'
4. Copy and paste all of the code shown below:Option ExplicitFunction SpellNumber(ByVal MyNumber)Dim Dollars, Cents, TempDim DecimalPlace, CountReDim Place(9) As StringPlace(2) = ' Thousand 'Place(3) = ' Million 'Place(4) = ' Billion 'Place(5) = ' Trillion 'MyNumber = Trim(Str(MyNumber))DecimalPlace = InStr(MyNumber, '.')If DecimalPlace > 0 ThenCents = GetTens(Left(Mid(MyNumber, DecimalPlace 1) _'00', 2))MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))End IfCount = 1Do While MyNumber
''Temp = GetHundreds(Right(MyNumber, 3))If Temp
'' Then Dollars = Temp Place(Count) DollarsIf Len(MyNumber) > 3 ThenMyNumber = Left(MyNumber, Len(MyNumber) - 3)ElseMyNumber = ''End IfCount = Count 1LoopSelect Case DollarsCase ''Dollars = 'No Dollars'Case 'One'Dollars = 'One Dollar'Case ElseDollars = Dollars ' Dollars'End SelectSelect Case CentsCase ''Cents = ' and No Cents'Case 'One'Cents = ' and One Cent'Case ElseCents = ' and ' Cents ' Cents'End SelectSpellNumber = Dollars CentsEnd FunctionFunction GetHundreds(ByVal MyNumber)Dim Result As StringIf Val(MyNumber) = 0 Then Exit FunctionMyNumber = Right('000' MyNumber, 3)If Mid(MyNumber, 1, 1)
'0' ThenResult = GetDigit(Mid(MyNumber, 1, 1)) ' Hundred 'End IfIf Mid(MyNumber, 2, 1)
'0' ThenResult = Result GetTens(Mid(MyNumber, 2))ElseResult = Result GetDigit(Mid(MyNumber, 3))End IfGetHundreds = ResultEnd FunctionFunction GetTens(TensText)Dim Result As StringResult = ''If Val(Left(TensText, 1)) = 1 ThenSelect Case Val(TensText)Case 10: Result = 'Ten'Case 11: Result = 'Eleven'Case 12: Result = 'Twelve'Case 13: Result = 'Thirteen'Case 14: Result = 'Fourteen'Case 15: Result = 'Fifteen'Case 16: Result = 'Sixteen'Case 17: Result = 'Seventeen'Case 18: Result = 'Eighteen'Case 19: Result = 'Nineteen'Case ElseEnd SelectElseSelect Case Val(Left(TensText, 1))Case 2: Result = 'Twenty 'Case 3: Result = 'Thirty 'Case 4: Result = 'Forty 'Case 5: Result = 'Fifty 'Case 6: Result = 'Sixty 'Case 7: Result = 'Seventy 'Case 8: Result = 'Eighty 'Case 9: Result = 'Ninety 'Case ElseEnd SelectResult = Result GetDigit _(Right(TensText, 1))End IfGetTens = ResultEnd FunctionFunction GetDigit(Digit)Select Case Val(Digit)Case 1: GetDigit = 'One'Case 2: GetDigit = 'Two'Case 3: GetDigit = 'Three'Case 4: GetDigit = 'Four'Case 5: GetDigit = 'Five'Case 6: GetDigit = 'Six'Case 7: GetDigit = 'Seven'Case 8: GetDigit = 'Eight'Case 9: GetDigit = 'Nine'Case Else: GetDigit = ''End SelectEnd Function
5. Return to your spreadsheet and single-click the cell containing a number you want converted into text.
6. Type '=SpellNumber(X)' (without quotation marks) into the cell, replacing 'X' with the number originally contained in the cell. You can also refer to the value of another cell, instead of a specific number (e.g., '=SpellNumber(B5)' to use the value in cell B5).
7. Press the 'Enter' key to convert the number value into letters. If you entered '7' in the previous step, for example, the value in the cell now displays as 'Seven.'