How to convert 100,000 to one hundred thousand in text?

[Susan Soo]

I would like to convert number into text. Any helps?

150,000 in Cell A and One Hundred Fifty Thousand Only in Cell D
100 in Cell A and One Hundred Only in Cell D

Please help.

Thanks

Susan

 

[texasalynn]

You might want to do a search on this forum as I have seend this question answered. It's a place to start any way.

texasalynn

 

[clipro]

Hi Susan,

Because I had the same problem a few days ago I think that this is the solution
http://www.mrexcel.com/board2/viewtopic.php?t=121813&highlight=convert+number+text
I didn't test it yet so if you test is please tell me if there are any kind of problems
I hope that those explanation is the right one

 

[Susan Soo]

Convert number to text. Help

Dear Friend,
I tried to copy the solution to my VBA but it won't work at all.

Please help.

Susan

Hi Susan,

Because I had the same problem a few days ago I think that this is the solution
http://www.mrexcel.com/board2/viewtopic.php?t=121813&highlight=convert+number+text
I didn't test it yet so if you test is please tell me if there are any kind of problems
I hope that those explanation is the right one

 

[clipro]

Hi Susan,

I try the code - it works just perfect (thanks to BJungheim
and Hotpepper) so what you must do:

1. open visual basic - from Tools>Macro>Visual Basic Editor
2. Right click on your project (par Example VBA Project(Book1.xls))
3. from the drop down menu choose Insert>Module
4. copy all code below and save
5. Turn back to xls
6. in one cell you have got the number (par example in cell A1 you have 1358) and in second one you want to see the number in text (so in B1 you want to see "one thousand three hundred fifty eight") In cell B1 click Insert>Function
7. choose "User Defined" and find SpellNumber
8. in the box you must enter A1. press Enter

Is that OK now? I hope that i helped you

Option Explicit

'****************
' Main Function *
'****************
Function SpellNumber(ByVal MyNumber)
Dim WholeNum, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " thousand "
Place(3) = " million "
Place(4) = " billion "
Place(5) = " trillion "

' String representation of amount
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, ".")
'Set MyNumber
If DecimalPlace > 0 Then
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then WholeNum = Temp & Place(Count) & WholeNum
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

SpellNumber = WholeNum
End Function

'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String

If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)

'Convert the hundreds place
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " hundred "
End If

'Convert the tens and ones place
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
Dim Result As String

Result = "" 'null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
Select 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 Else
End Select
Else ' If value between 20-99
Select 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 Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) 'Retrieve ones place
End If
GetTens = Result
End Function

'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function 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 Select
End Function

 

[Susan Soo]

Dear Clipro,

I follow your easy to follow instruction and the codes work fine.

Unfortunately, I have to repeat the same procedure that is to insert function, choose user defined, and select spell number again and again.

I need to convert approximately 200 numbers into text per day for my job.

Kindly let me know whether there is any way to assign those codes to a macro. Than, I would be able to just change the number in Cell A1 and the text number automatically appear in Cell B1.

Your attention to my matter is most highly appreciated.

Thanking you,

Susan

Hi Susan,

I try the code - it works just perfect (thanks to BJungheim
and Hotpepper) so what you must do:

1. open visual basic - from Tools>Macro>Visual Basic Editor
2. Right click on your project (par Example VBA Project(Book1.xls))
3. from the drop down menu choose Insert>Module
4. copy all code below and save
5. Turn back to xls
6. in one cell you have got the number (par example in cell A1 you have 1358) and in second one you want to see the number in text (so in B1 you want to see "one thousand three hundred fifty eight") In cell B1 click Insert>Function
7. choose "User Defined" and find SpellNumber
8. in the box you must enter A1. press Enter

Is that OK now? I hope that i helped you

Option Explicit

'****************
' Main Function *
'****************
Function SpellNumber(ByVal MyNumber)
Dim WholeNum, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " thousand "
Place(3) = " million "
Place(4) = " billion "
Place(5) = " trillion "

' String representation of amount
MyNumber = Trim(Str(MyNumber))

' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, ".")
'Set MyNumber
If DecimalPlace > 0 Then
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then WholeNum = Temp & Place(Count) & WholeNum
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

SpellNumber = WholeNum
End Function

'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String

If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)

'Convert the hundreds place
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " hundred "
End If

'Convert the tens and ones place
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
Dim Result As String

Result = "" 'null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
Select 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 Else
End Select
Else ' If value between 20-99
Select 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 Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) 'Retrieve ones place
End If
GetTens = Result
End Function

'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function 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 Select
End Function

 

[clipro]

Hi Susan,

You must change more than 200 numbers per day so these numbers are they in one sheet (in one column) because if they are you just need to copy the formula bellow - you do not need a macro

otherwise if you must to put each number in cell A1 manually the formula works

if you want give us an example of your sheet with example data of cource