Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Converting From Numbers to Text.

Converting From Numbers to Text

by Allen Wyatt
(last updated March 7, 2017)

14

Jocelyn asked if there is a way to easily convert numbers to their text equivalent. For instance, to convert the numeric value 6789 to the text digits "6789."

There are multiple ways that you can approach this problem. One way is to simply specify to Excel that the cells should be treated as text. Display the Format Cells dialog box and then on the Number tab make sure that Text is selected. Any selected cells are then formatted as if they were text, and are moved to the left side of their cells (unless you have some other alignment format specified).

The applicability of this solution, however, depends on the version of Excel you are using. In some versions it will work as described here, but in others it may not. There is another thing you can try, however:

  1. Select the cells you want to convert.
  2. Display the Home tab of the ribbon.
  3. Click the Format tool in the Cells group, then choose Format Cells.
  4. Make sure the Number tab is selected. (See Figure 1.)
  5. Figure 1. The Number tab of the Format Cells dialog box.

  6. In the list of formatting Categories, choose Text.
  7. Click on OK.
  8. Press Ctrl+C. This copies the cells you selected in step 1.
  9. Display the Home tab of the ribbon.
  10. Click the down-arrow under the Paste tool and choose Paste Special from the resulting choices. Excel displays the Paste Special dialog box.
  11. Choose the Values radio button.
  12. Click on OK.

Another thing you can do is to use the TEXT worksheet function. Let's say you have the value 6789 in cell A7. In cell B7 you could place the following:

=TEXT(A1, "#,##0.00")

This usage of TEXT results in a text digits that have the thousands separator in the right place and two digits to the right of the decimal point, as in "6,789.00". You can specify, as the second parameter to TEXT, any formatting desired. If you want the number converted to text without any special formatting, you can use the following:

=TEXT(A1, "0")

The result is the textual equivalent of an integer value.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6230) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Converting From Numbers to Text.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Adding Caption Labels

When using the captioning capabilities of Word, you aren't limited to the three default caption labels provided in the ...

Discover More

Automatic Scrolling

Spend a lot of time scrolling around in your document? You might find one of Word's hidden scrolling commands to be a nice ...

Discover More

Conditionally Formatting for Multiple Date Comparisons

When you compare dates in a conditional formatting rule, you need to be careful how you put your comparisons together. Do it ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Changing Fonts in Multiple Workbooks

If you need to change fonts used in a lot of different workbooks, the task can be daunting, if you need to do it manually. ...

Discover More

Changing Cell Patterns

You can shade your cells by filling them with a pattern. Here's how to select the pattern you want used.

Discover More

Adjusting Center Across Selection with a Cell Value

One of the most common cell alignment settings I use is the one to center across a selection. If you want to vary how this ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 0?

2017-03-07 06:38:55

Arnstein

Number to TEXT

=A1&""


2017-03-07 04:56:38

Ken Varley

Typo......
Formula refers to cell A1
Explaination refers to cell A7


2016-06-25 03:44:01

Anand Mundada

In the Cell A1 has value of 5, i want to convert in to text i.e. pass, if value is 3 ATKT and if value is 4 Fail in same cell or in another cell. How it can be done


2016-06-25 01:14:51

sandip

Dear sir,
How can l convert number to text. E.g 1500 in cell C1 and the result in C4 should "one thousands five hundred only"


2016-05-08 19:16:05

Gil Fabia

How can I put into a cell the number of times i opened the worksheet or an excel file? Everytime i open a file 1 is added to to a specific cell say A1. Cell A1 will be showing 20 if the file is opened at the 20th times. If i open it on the 21st time then, A1 will be 22. Thank you and more power.


2015-12-29 09:12:53

Rajnikant Padole

1234

Formula


=BAHTTEXT(A2)
Displays the number in text. (One thousand two hundred thirty four Baht in Thai text)
result - หนึ่งพันสองร้อยสามสิบสี่บาทถ้วน

please text show in u.s. english


2015-09-04 07:31:32

Prodrigues

Well, to get text from a number (ex: 56 return fifty six) i use this function.
But i am portuguese, so it works for portuguese numbers.

I have no time to explain more, but could be important, and you can adapt.
Further more, the funtion works, but i have intention to improve the function (when i have the time).


syntax to use in portuguese:

=extenso(nº, type)

nº= number.
Range: 0 to 999999999.99
Two decimals (max)
type= -1 or 0 or 1 or 2
-1 = none
0 = text for units: number
1 = text for units: Euros
2 = text for units: Reais (UM Brasil)
>2 = 0

Function
************

Function extenso(nValor As String, nPNR_Tipo As Integer) As String


If IsNull(nValor) Or nValor > 999999999.99 Then Exit Function


Dim PNR_Tipo As Integer
Dim PNR_Unid_Plural As String
Dim PNR_Unid_Singular As String
Dim PNR_Cent_Plural As String
Dim PNR_Cent_Singular As String


If nPNR_Tipo > 2 Then
nPNR_Tipo = 0
End If

PNR_Tipo = nPNR_Tipo

If PNR_Tipo = 0 Then
PNR_Unid_Plural = "unidades "
PNR_Unid_Singular = "unidade "
PNR_Cent_Plural = "centésimas"
PNR_Cent_Singular = "centésima"
End If

If PNR_Tipo = 1 Then
PNR_Unid_Plural = "Euros "
PNR_Unid_Singular = "Euro "
PNR_Cent_Plural = "Cêntimos"
PNR_Cent_Singular = "Cêntimo"
End If

If PNR_Tipo = 2 Then
PNR_Unid_Plural = "Reais "
PNR_Unid_Singular = "Real "
PNR_Cent_Plural = "Centavos"
PNR_Cent_Singular = "Centavo"
End If

If PNR_Tipo = -1 Then
PNR_Unid_Plural = ""
PNR_Unid_Singular = ""
PNR_Cent_Plural = ""
PNR_Cent_Singular = ""
End If

'Declara as variáveis da função
Dim intContador As Integer
Dim intTamanho As Integer
Dim strValor As String
Dim strParte As String

Dim strFinal As String
Dim strGrupo(4) As String
Dim strTexto(4) As String

'Define matrizes com extensos parciais
Dim strUnid(19) As String
strUnid(1) = "um ": strUnid(2) = "dois ": strUnid(3) = "três ": _
strUnid(4) = "quatro ": strUnid(5) = "cinco ": strUnid(6) = "seis ": strUnid(7) = "sete ": _
strUnid(8) = "oito ": strUnid(9) = "nove ": strUnid(10) = "dez ": strUnid(11) = "onze ": _
strUnid(12) = "doze ": strUnid(13) = "treze ": strUnid(14) = "catorze ": _
strUnid(15) = "quinze ": strUnid(16) = "dezasseis ": strUnid(17) = "dezassete": _
strUnid(18) = "dezoito ": strUnid(19) = "dezanove "

Dim strDezena(9) As String
strDezena(1) = "dez ": strDezena(2) = "vinte ": strDezena(3) = "trinta ": _
strDezena(4) = "quarenta ": strDezena(5) = "cinquenta ": strDezena(6) = "sessenta ": _
strDezena(7) = "setenta ": strDezena(8) = "oitenta ": strDezena(9) = "noventa "

Dim strCentena(9) As String
strCentena(1) = "cento ": strCentena(2) = "duzentos ": strCentena(3) = "trezentos ": _
strCentena(4) = "quatrocentos ": strCentena(5) = "quinhentos ": strCentena(6) = "seiscentos ": _
strCentena(7) = "setecentos ": strCentena(8) = "oitocentos ": strCentena(9) = "novecentos "


'Divide o valor em vários grupos
strValor = Format$(nValor, "0000000000.00")
strGrupo(1) = Mid$(strValor, 2, 3) 'Milhão
strGrupo(2) = Mid$(strValor, 5, 3) 'Milhar
strGrupo(3) = Mid$(strValor, 8, 3) 'Centena
strGrupo(4) = "0" + Mid$(strValor, 12, 2) 'Centavo

'Processa cada grupo
For intContador = 1 To 4
strParte = strGrupo(intContador)
intTamanho = Switch(Val(strParte) < 10, 1, Val(strParte) < 100, 2, Val(strParte) < 1000, 3)
If intTamanho = 3 Then
If Right$(strParte, 2) <> "00" Then
strTexto(intContador) = strTexto(intContador) + strCentena(Left(strParte, 1)) + "e "
intTamanho = 2
Else
strTexto(intContador) = strTexto(intContador) + IIf(Left$(strParte, 1) = "1", "cem ", strCentena(Left(strParte, 1)))
End If
End If

If intTamanho = 2 Then
If Val(Right(strParte, 2)) < 20 Then
strTexto(intContador) = strTexto(intContador) + strUnid(Right(strParte, 2))
Else
strTexto(intContador) = strTexto(intContador) + strDezena(Mid(strParte, 2, 1))
If Right$(strParte, 1) <> "0" Then
strTexto(intContador) = strTexto(intContador) + "e "
intTamanho = 1
End If
End If
End If

If intTamanho = 1 Then
strTexto(intContador) = strTexto(intContador) + strUnid(Right(strParte, 1))
End If
Next intContador


'Gera o formato final do texto
If Val(strGrupo(1) + strGrupo(2) + strGrupo(3)) = 0 And Val(strGrupo(4)) <> 0 Then
strFinal = strTexto(4) + IIf(Val(strGrupo(4)) = 1, PNR_Cent_Singular, PNR_Cent_Plural)
Else
strFinal = ""
If Val(strGrupo(2)) = 0 And Val(strGrupo(3)) = 0 And Val(strGrupo(4)) = 0 Then
strFinal = strFinal + IIf(Val(strGrupo(1)) <> 0, strTexto(1) + IIf(Val(strGrupo(1)) > 1, "milhões de ", "milhão de "), "")
End If
If Val(strGrupo(2)) <> 0 And Val(strGrupo(3)) = 0 And Val(strGrupo(4)) = 0 Then
strFinal = strFinal + IIf(Val(strGrupo(1)) <> 0, strTexto(1) + IIf(Val(strGrupo(1)) > 1, "milhões e ", "milhão e "), "")
End If

If Val(strGrupo(2)) = 0 And Val(strGrupo(3)) <> 0 And Val(strGrupo(4)) = 0 Then
strFinal = strFinal + IIf(Val(strGrupo(1)) <> 0, strTexto(1) + IIf(Val(strGrupo(1)) > 1, "milhões e ", "milhão e "), "")
End If

If Val(strGrupo(2)) <> 0 And Val(strGrupo(3)) <> 0 And Val(strGrupo(4)) = 0 Then
strFinal = strFinal + IIf(Val(strGrupo(1)) <> 0, strTexto(1) + IIf(Val(strGrupo(1)) > 1, "milhões, ", "milhão, "), "")
End If

If Val(strGrupo(2)) <> 0 And Val(strGrupo(3)) <> 0 And Val(strGrupo(4)) <> 0 Then
strFinal = strFinal + IIf(Val(strGrupo(1)) <> 0, strTexto(1) + IIf(Val(strGrupo(1)) > 1, "milhões, ", "milhão, "), "")
End If

If Val(strGrupo(2)) <> 0 And Val(strGrupo(3)) = 0 And Val(strGrupo(4)) <> 0 Then
strFinal = strFinal + IIf(Val(strGrupo(1)) <> 0, strTexto(1) + IIf(Val(strGrupo(1)) > 1, "milhões, ", "milhão, "), "")
End If

If Val(strGrupo(2)) = 0 And Val(strGrupo(3)) = 0 And Val(strGrupo(4)) <> 0 Then
strFinal = strFinal + IIf(Val(strGrupo(1)) <> 0, strTexto(1) + IIf(Val(strGrupo(1)) > 1, "milhões de ", "milhão de"), "")
End If

If Val(strGrupo(2)) = 0 And Val(strGrupo(3)) <> 0 And Val(strGrupo(4)) <> 0 Then
strFinal = strFinal + IIf(Val(strGrupo(1)) <> 0, strTexto(1) + IIf(Val(strGrupo(1)) > 1, "milhões, ", "milhão, "), "")
End If

If Val(strGrupo(3)) = 0 Then
strFinal = strFinal + IIf(Val(strGrupo(2)) <> 0, strTexto(2) + "mil ", "")
Else
If Val(strGrupo(4)) = 0 Then
strFinal = strFinal + IIf(Val(strGrupo(2)) <> 0, strTexto(2) + "mil e ", "")
Else
strFinal = strFinal + IIf(Val(strGrupo(2)) <> 0, strTexto(2) + "mil, ", "")
End If
End If

If Val(strGrupo(4)) = 0 Then
strFinal = strFinal + strTexto(3) + IIf(Val(strGrupo(1) + strGrupo(2) + strGrupo(3)) = 1, PNR_Unid_Singular, PNR_Unid_Plural)
Else
strFinal = strFinal + strTexto(3) + IIf(Val(strGrupo(3)) <> 1, IIf(Val(strGrupo(1) + strGrupo(2) + strGrupo(3)) = 1, PNR_Unid_Singular, PNR_Unid_Plural), PNR_Unid_Singular)
End If



strFinal = strFinal + IIf(Val(strGrupo(4)) <> 0, "e " + strTexto(4) + IIf(Val(strGrupo(4)) = 1, PNR_Cent_Singular, PNR_Cent_Plural), "")

End If

'If Left(strFinal, 1) = "u" Then
' extenso = "H" & Mid$(strFinal, 1)
'Else
extenso = UCase(Mid$(strFinal, 1, 1)) & Mid$(strFinal, 2)
'End If

Dim aux As String
''* 150
aux = Trim(extenso)
'' e alterar esta linha para trim(extenso)
' While Len(Trim(aux)) <> 150
' aux = Trim(aux) & "-$"
' Wend
extenso = LCase(aux)


End Function



2015-09-04 03:50:08

pravin patil

1234

Formula


=BAHTTEXT(A2)
Displays the number in text. (One thousand two hundred thirty four Baht in Thai text)
result - หนึ่งพันสองร้อยสามสิบสี่บาทถ้วน

please text show in u.s. english


2015-08-01 05:47:55

Kavitha

I Want the Expanded value of numbers
for ex.
If i key 14153.23
the net result should be

Fourteen Thousand One Hundred and Fifty Three and Twenty Three

Please Help


2015-06-05 08:50:19

Liquid acer

@vicky

check this link for sample of converting nos. into words in Excel
HOPE this help

https://youtube.com/watch?v=gTQiNAk6YxE


2015-04-28 11:37:49

Glenn Case

Vicky:

Check out http://excelribbon.tips.net/T008351_Converting_Numbers_Into_Words.html


2015-04-25 10:38:40

Michael

Conceptually, you could do it in a loop, digit by digit, starting from the right, concatenating results in a leftwise direction, picking the text from a table. The single digit zero and the tens digits are special cases which complicate things a bit. Probably better done in Office C# or VBA, which, regrettably, is above my pay grade.


2015-04-24 15:25:10

Sten-Sture

I have a column with 650 rows with contents like the following 07:12 and Excel thinks it is a number, I want to convert it to a text string!
The reason is that I have some other text values in the same column, and like to sort all om the as text values.


2015-04-24 07:47:57

VICKY

I want the value of cell C2 is 1500 to be converted as 'One thousand five hundred" in cell C5


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.