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

#### Susan Soo

##### New Member
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

Thanks

Susan ### 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
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
Convert number to text. Help

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

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

##### Board Regular

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
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

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
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

Replies
1
Views
216
Replies
3
Views
291
Replies
2
Views
159
Replies
172
Views
2K
Replies
2
Views
107

### Forum statistics

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.

### Which adblocker are you using?    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

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