Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Converting From Numbers to Text

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

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. Choose Cells from the Format menu. Excel displays the Format Cells dialog box. (To display the dialog box in Excel 2007, display the Home tab of the ribbon, click Format in the Cells group, and then choose Format Cells.)
  3. Make sure the Number tab is selected. (See Figure 1.)
  4. Figure 1. The Number tab of the Format Cells dialog box.

  5. In the list of formatting Categories, choose Text.
  6. Click on OK.
  7. Press Ctrl+C. This copies the cells you selected in step 1.
  8. Choose Paste Special from the Edit menu. Excel displays the Paste Special dialog box.
  9. Choose the Values radio button.
  10. 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 (2680) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Converting From Numbers to Text.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

AL    07 Sep 2016, 10:49
could you take this tip one step further, and explain how to preserve leading zeros when converting from fixed length number to text? the only thing that works for me is to copy the column to notepad, format a column at text and copy from notepad back to excel
islam    25 Aug 2016, 04:35
Please I Need Your Help

How to Convert currency With Three Decimal Digits

For Example :

125.325 KD

To Be:
One Hundred Twenty Five Dinar & three Hundred Twenty Five Fils Only

Or

One Hundred Twenty Five Dinar & 325 Fils only
SUBRAT KUMAR PATTANAYAK    15 Aug 2016, 11:43
Dear sir,

Please let me know how to convert number to word such as

5,117 (Five Thousand One Hundred Seventeen)

100 (Hundred)
Teresa    02 Aug 2016, 10:31
Thank you!!! This =TEXT(A2, "0") function saved my sanity!
I don't know why Excel doesn't just allow a change un formatting, requiring it to be "applied" so to speak. Makes no logical sense, nor does the whole need for this formula because of scientific notation being default in Excel!
eric    07 Jul 2016, 00:47
Dear Sir,

Is there any chance to add/modify belows to make it more perfect?

i. add "-" for double digits like 28 as twenty-eight (instead of twenty eight);

ii. put "and cents .xx only" instead of "and .xx cents only" (also add "ONLY");

iii. like $280, put "And" after Two Hundred, and before Eighty ? Can it simply shows "ONLY" and don't need "No Cents" ? The original is Two Hundred Eighty Dollars and No Cents.

Thanks so much.
 
Andrés    19 Jun 2016, 11:11
Hello Mervyn, I know two ways to get what you want
-Using Custom Format cells
-Using a macro:

Sub AddZeros()
ActiveCell.NumberFormat = "@" 'Convert activecell into text
    l = 6 'length
    c = ActiveCell
    n = Len(c)
    nn = l - n
    For i = 1 To nn
        c = "0" & c
    Next
    ActiveCell = c
End Sub

The variable "l" can vary to any number you want.
Mervyn    03 Jun 2016, 05:02
Sir,

I would like to convert the number 123 to the following text value: *00123*
Please assist...
Thank you.
anitha    29 Apr 2016, 07:55
sir ,
i want to convert a number to text
example 225 as two hundred twenty five only. please send me answer to my email.
Michael (Micky) Avidan    17 Apr 2016, 06:05
@Dinesh Rawat,
Take a look at my post dated: 28 Dec 2014, 10:06
-----------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016
ISRAEL
Dinesh Rawat    16 Apr 2016, 15:45
Dear sir,

Please let me know how to convert number to word such as

5 five
100 hundred
sheena    07 Mar 2016, 01:27
hi

i would like to know following comemnts

how to do v look up, h look up, & converting numeric to words
Michael (Micky) Avidan    21 Jan 2016, 11:17
@nabajyoti das,
Take a look at my post dated: 28 Dec 2014, 10:06
-----------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016
ISRAEL
nabajyoti das    20 Jan 2016, 09:27
Dear Sir,

Please tell me that how to convert number to word for example -

1, one
12 twelve
200 two hundred
5010 five thousand ten
Michael (Micky) Avidan    13 Jan 2016, 05:27
@SYED ESAK,
@T UMAMAHESHWAR RAO,
Check out the following link:
https://support.microsoft.com/en-us/kb/213360
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL
SYED ESAK    13 Jan 2016, 02:22
pl how to convert numeric to text
T UMAMAHESHWAR RAO    09 Jan 2016, 09:47
sir ,
i want to convert a number to text
example 225 as two hundred twenty five only. please send me answer to my email.
thanq
Michael (Micky) Avidan    05 Dec 2015, 07:42
@Sonu Bhanushali,
Take a look at my post dated: 28 Dec 2014, 10:06
-----------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016
ISRAEL
Sonu Bhanushali    05 Dec 2015, 03:07
Dear Sir,

Please tell me that how to convert number to word for example -

1, one
12 twelve
200 two hundred
5010 five thousand ten
Manoj Falak    18 Oct 2015, 05:50
Sir, want the formula of cell A1 is Date 21th march 2014 is to be converted as 'twenty first march two thousand forteen" in cell B1.
Michael (Micky) Avidan    02 Oct 2015, 05:32
@Yogini & Rohiz,
Take a look at my post dated: 28 Dec 2014, 10:06
-----------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016
ISRAEL
Rohiz     02 Oct 2015, 01:28
Dear Sir,

Please tell me that how to convert number to word for example -

1, one
12 twelve
200 two hundred
5010 five thousand ten

 i think you understood very well.

thank you
Yogini    19 Aug 2015, 06:27
We have a Invoice format, where i want numeric total value to directly convert to alphabetic total value, in the below cell of the numeric total.

For Ex:
xyz sold 2500



Tax 5% 125
Total 2625
Rupees (in words): _______________

So i want 2625 or whatsoever total is should change to text in below cell of Rupees (in words):

How it can be done?
Suggest me
Michael (Micky) Avidan    17 Aug 2015, 07:21
@Praveen Kumar,
Should the following 3 strings be in one cell or in 3 cells in a row?
------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL
Praveen Kumar    17 Aug 2015, 01:40
Hi Can anyone help me this out ?
 
In excel I need a set of numbers display automatically as following:

 143 as One Four Three
 245 as Two Four Five

Is there any formula?



 
Anurag Mishra    10 Aug 2015, 04:41
Dear Sir,

I need to convert 500.00 to five hundred repees.
vinay t    30 Jul 2015, 07:36
Dear Sir,

want the value of cell A1 is some amount 5500 to be converted as 'Five thousand Five hundred" in cell B1. once i enter the numaric letters in cell A1.
vinayaka.T    17 Jul 2015, 10:18
Sir, want the value of cell A1 is some amount 20,000 to be converted as 'twenty thousand " in cell B1. once i enter the numaric letters in cell A1.
stcatubag@profriends.com    03 Jun 2015, 12:16
@SARATH
check this link for sample of converting nos. into words in Excel

 https://youtube.com/watch?v=gTQiNAk6YxE
Shah    20 May 2015, 15:14
@Micky
the tip http://support.microsoft.com/kb/213360/en-us did the trick for converting a number to English words. Thank you!!!
SARATH    19 May 2015, 05:17
Sir, want the value of cell A1 is some amount 5500 to be converted as 'Five thousand Five hundred" in cell B1. once i enter the numaric letters in cell A1.
Talha Farooq    29 Apr 2015, 18:08
hi, it is possible to form developer bottom how much time i needed.

For example:

If I wrote 5 then automatically generated 5 deveolper botton in next row.
prashant    28 Feb 2015, 00:47
want to convert no into text in excel

600=text automatically???

Koketso    27 Feb 2015, 05:05
Hi Allen

In my case i converted numbers to text but somehow excel still recognizes this as numbers...for example when i want to sort the columns, it wants to sort from smallest to largest rather than from A to Z etc

Prabin Bhandaru    19 Jan 2015, 09:47
Sir, want the value of cell A1 is some amount 5500 to be converted as 'Five thousand Five hundred" in cell B1. once i enter the numaric letters in cell A1.
Hrushikesh     03 Jan 2015, 09:09
Sir, want the value of cell is some amount like 97,200 to be converted as 'Ninety Seven Thousand Two Hundred", once i enter the numaric letters in any cell.
Michael (Micky) Avidan    28 Dec 2014, 10:06
@RANJIT KAKATI,
@Francis,
@AJay,
Take a look at the following link:
http://support.microsoft.com/kb/213360/en-us
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
Basavaraj    27 Dec 2014, 10:07
Hello Sir,

Taking your example 6789 for converting from numbers to text, I have another situation can you please help me to solve this below

First I have given manually typed code to the numbers i.e For 6 code is FH, 7 is GI, 8 is HJ, 9 is IK.

In A1 cell is type 6789, it should reflect/display as FH-GI-HJ-IK in B1 cell.

Column A Column B
6789 FH-GI-HJ-IK
7896 GI-HJ-IK-FH
8967 HJ-IK-FH-GI
8888 HJ-HJ-HJ-HJ
9999 formula ?

Please suggest a formula when I enter a number in Column A and code should reflect/Display in Column B

Thanks.



RANJIT KAKATI    25 Dec 2014, 03:17
Sir, want the value of cell A1 is some amount 191641 to be converted as 'One lakh ninety one thousand six hundred forty one" in cell B1. once i enter the numaric letters in cell A1.
Francis    09 Nov 2014, 02:20
Sir, want the value of cell A1 is some amount 5500 to be converted as 'Five thousand Five hundred" in cell B1. once i enter the numaric letters in cell A1.
AJay    03 Nov 2014, 02:10
I want the value of cell C2 is 1500 to be converted as 'One thousand five hundred" in cell C5
Michael (Micky) Avidan    20 Nov 2013, 05:18
@Steve N,
There is a very famous saying: "all roads leads to Rome" - but most people will take the fastest/shortest route.
Same in "Excel".
If you will try my suggestion and count its steps - I'm sure that also you will easily decide which procedure you're going to use in the future.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
Steve N.    19 Nov 2013, 08:08
I have found that after Allen's step 5, you can just press the F2 key (edit mode), make no changes and then press enter. The value of the cell will behave and act as if it were just text.

That only works for one number at a time. If you are trying to apply this to multiple cells, there is not a true one cell fix. If you don't want to spend the time repeating the F2>enter repair multiple number of times, I'd then follow the rest of Allen's solution.
Michael (Micky) Avidan    18 Nov 2013, 07:23
Usually, I use a different approach.

Select the range of cells > goto the menu 'Data' > 'Text to Columns' > 'Next' > 'Next' > select the "TEXT" radio button > 'Finish'.

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL
Lois    17 Nov 2013, 11:28
Hi Allen, Thanks for this. In my scenario in the same column I had values that were all different types, some alpha some numeric so I just used
= TEXT (A1,"#")

thanks for the tip. this was a great solution to a column where I couldn't use the "Convert to number" pop up because of the differnt data types. Hours of my life saved. :)
vijay kumar     23 May 2013, 06:41
sir ms excel 2003 me convert number to text bataiye
Ahmed    04 Dec 2012, 01:29
Dear Sir,

Thanks for you help and what i need is to convert for example 570.00 to Five Hundred Seventy Dollars Only

Regards
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.