# 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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

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

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

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
19
Views
662
Replies
5
Views
307
Replies
3
Views
170
Replies
0
Views
272
Replies
3
Views
181

1,181,783
Messages
5,932,022
Members
436,814
Latest member
Yurop

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