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

Susan Soo

New Member
Joined
Nov 13, 2004
Messages
33
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 :cry:
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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
 

Susan Soo

New Member
Joined
Nov 13, 2004
Messages
33

clipro

Board Regular
Joined
Jan 12, 2005
Messages
195

ADVERTISEMENT

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

New Member
Joined
Nov 13, 2004
Messages
33
Dear Clipro,

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

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. :rolleyes:

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. :rolleyes:

Your attention to my matter is most highly appreciated.

Thanking you,

Susan





clipro said:
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

Board Regular
Joined
Jan 12, 2005
Messages
195
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
 

Forum statistics

Threads
1,148,523
Messages
5,747,180
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top