by This tutorial shows how to convert number to text in Excel 2016, 2013 and 2010. See how to accomplish the task with the Excel TEXT function and use number to string to specify the formatting. Learn how to change number format to text with the Format Cells… and Text to Columns options.
If you use Excel spreadsheets to store long and not so long numbers, one day you may need to convert them to text. There may be different reasons to change digits stored as numbers to text. Below you'll find why you may need to make Excel see the entered digits as text, not as number.
- Search by part not by the entire number. For example, you may need to find all numbers that contain 50, like in 501, 1500, 1950, etc.)
- It may be necessary to match two cells using the VLOOKUP or MATCH function. However, if these cells are formatted differently, Excel will not see identical values as matching. For instance, A1 is formatted as text and B1 is number with format 0. The leading zero in B2 is a custom format. When matching these 2 cells Excel will ignore the leading 0 and will not show the two cells as identical. That's why their format should be unified.
The same issue can occur if the cells are formatted as ZIP code, SSN, telephone number, currency, etc.
Note. If you want to convert numbers to words like amount to text, it's a different task. Please check the article about spelling numbers named Two best ways to convert numbers to words in Excel.
In this article I'll show you how to convert numbers to text with the help of the Excel TEXT function. If you are not so formula-oriented, have a look at the part where I explain how to change digits to text format with the help of the standard Excel Format Cells window, by adding an apostrophe and employing the Text to Columns wizard.
convert-number-to-text-excel-TEXT-function
Convert number to text using the Excel TEXT function
The most powerful and flexible way to convert numbers to text is using the TEXT function. It turns a numeric value into text and allows to specify the way this value will be displayed. It's helpful when you need to show numbers in a more readable format, or if you want to join digits with text or symbols. The TEXT function converts a numeric value to formatted text, thus the result cannot be calculated.
If you are familiar with using formulas in Excel, it will not be a problem for you to employ the TEXT function.
- Add a helper column next to the column with the numbers to format. In my example, it's column D.
- Enter the formula
=TEXT(C2,"0")to the cell D2. In the formula, C2 is the address of the first cell with the numbers to convert. - Copy the formula across the column using the fill handle.
- You will see the alignment change to left in the helper column after applying the formula.
- Now you need to convert formulas to values in the helper column. Start with selecting the column.
- Use Ctrl + C to copy. Then press the Ctrl + Alt + V shortcut to display the Paste Special dialog box.
- On the Paste Special dialog, select the Values radio button in the Paste group.
You will see a tiny triangle appear in the top-left corner of each cell in your helper column, which means the entries are now text versions of the numbers in your main column.
Now you can either rename the helper column and delete the original one, or copy the results to your main and remove the temporary column.
Note. The second parameter in the Excel TEXT function shows how the number will be formatted before being converted. You may need to adjust this based on your numbers:
The result of
=TEXT(123.25,"0")will be 123.The result of
=TEXT(123.25,"0.0")will be 123.3.The result of
=TEXT(123.25,"0.00")will be 123.25.To keep the decimals only, use
=TEXT(A2,"General").Tip. Say you need to format a cash amount, but the format isn't available. For instance, you cannot display a number as British Pounds (£) as you use the built-in formatting in the English U.S. version of Excel. The TEXT function will help you convert this number to Pounds if you enter it like this:
=TEXT(A12,"£#,###,###.##"). Just type the format to use in quotes -> insert the £ symbol by holding down Alt and pressing 0163 on the numeric keypad -> type #,###.## after the £ symbol to get commas to separate groups, and to use a period for the decimal point. The result is text!
Use the Format Cells option to convert number to text in Excel
If you need to quickly change the number to string, do it with the Format Cells… option.
- Select the range with the numeric values you want to format as text.
- Right click on them and pick the Format Cells… option from the menu list.
Tip. You can display the Format Cells… window by pressing the Ctrl + 1 shortcut.
- On the Format Cells window select Text under the Number tab and click OK.
You'll see the alignment change to left, so the format will change to text. This option is good if you don't need to adjust the way your numbers will be formatted.
Add an apostrophe to change number to text format
If these are just 2 or 3 cells in Excel where you want to convert numbers to string, benefit from adding an apostrophe before the number. This will instantly change the number format to text.
Just double-click in a cell and enter the apostrophe before the numeric value.
You will see a small triangle added in the corner of this cell. This is not the best way to convert numbers to text in bulk, but it's the fastest one if you need to change just 2 or 3 cells.
Convert numbers to text in Excel with Text to Columns wizard
You may be surprised but the Excel Text to Columns option is quite good at converting numbers to text. Just follow the steps below to see how it works.
- Select the column where you want to convert numbers to string in Excel.
- Navigate to the Data tab in and click on the Text to Columns icon.
- Just click through steps 1 and 2. On the third step of the wizard, make sure you select the Text radio button.
- Press Finish to see your numbers immediately turn into text.
I hope the tips and tricks from this article will help you in your work with numeric values in Excel. Convert number to string using the Excel TEXT function to adjust the way your numbers will be displayed, or use Format Cells and Text to Columns for quick conversions in bulk. If these are just several cells, add an apostrophe. Feel free to leave your comments if you have anything to add or ask. Be happy and excel in Excel!
163 comments
I have numbers with preceeding zero in a coloumn. For example, 01111,01112,01113....etc.,.Now i want to convert these numbers along with preceeding zero as text. The resultant text should also be 01111,01112,01113....etc.,.with tiny green triangle at the top left corner of the cells. How to do it?
Hi! If I understand the problem correctly, you can use the TEXT function and the number format you set in those cells. For example,
=TEXT(A1,"00000")
For more information, read: How to add leading zeros in Excel.
It works fine.But,why a tiny green triangle is not displayed when the number in"00000" format is converted into text using the formula:=text(A1,"00000" ?
If you want to see the green triangle, replace formulas with their values. Do Copy and then Paste Values with these cells.
There is any formula to convert the Number to text and number (both)
Eg: US$ 371.25 should display as (Three Hundred Seventy One US Dollars and fils 25/100 Only)
Thanks
Hey! you need to take the support of VBE tool. To start with VBE Tool. Follow These Steps
Step 1: Use the keyboard shortcut, Alt + F11 to open the Visual Basic Editor (VBE).
Step 2: Click the Insert tab, and click Module.
Step 3: Paste Following Codes (Now Since I am Dealing in Rupee and Paise I have added Rupee and Paise You can change These Flags to US Dollars and Fils in the these code. for example. Replace US Dollar with Rupee and Fils with Paise.
Here is the code:
-----------------------------------
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Rupees, Paise, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lakh "
Place(4) = " Crore "
Place(5) = " Thousand Crore "
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Rupees
Case ""
Rupees = ""
Case "One"
Paise = "One Paise"
Case Else
Rupees = Rupees & " Rupees"
End Select
Select Case Paise
Case ""
Paise = ""
Case "One"
Paise = ""
Case Else
Paise = " and " & Paise & " Paise"
End Select
SpellNumber = Rupees & Paise
End Function
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
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 = "Fourty "
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
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
-------------------------------------------------
Step 4: Save the worksheet as macro enabled sheet while saving
Step 5: Go back to your sheet by pressing alt+Q
Step 6: Now you can use formula of SpellNumber=() here, in the bracket you can use specific number or define cell which you want to translate.
it will be done.
Hope this will work for you!
I'm looking to assign an number to a word and then add those numbers?
so for example:
High = 1
Med = .6
Low = .3
Any guidance? Would love them numbers to then add up to a total.
Hello!
Unfortunately, this information is not enough to recommend a formula to you. Since you want to do conditional calculations, use the IF function and these guidelines: Nested IF in Excel – formula with multiple conditions.
My formular is =225+22 in Cell A25, i want to see =225+22 in Cell B25. how is it possible
Set format of cell B25 to text and copy and paste formula from cell A25
Hi
I am currently working with international NGO in Afghanistan, and I wanted to excel to convert number in to text in the other cell, despite I have tried several times and followed different directions in result I would not be able to successfully overcome?
Hello!
i am currently working on a task in which i want excel to see a particular text as a number.
Specifically i want a situation where anytime the text N is on any cell in the spreadsheet, excel sees it as -0.5, and whenever there is a Y on the spreadsheet, it sees it as 0.
How do i do that?
Hi!
If you want to automatically replace the text in a cell with a number, you can do this using the Find & Replace tool or using a macro.
I have a need to create a short name from the last name on file. I can use the left function but when creating the name I need spaces for any remaining positions that is not used of the six characters. How do you fill right with spaces?
Hi!
Count the number of characters using the LEN function. To add the required number of spaces, use the REPT function
=A1&IF(LEN(A1)<6,REPT(" ",6-LEN(A1)),"")
Hi,
I'm to incorporate the number from particular cell in Excel into the sentence using the text function but I want to have different Decimal/thousand separators to be used in a sentance displayed e.g. 6.6% to be presented in a sentance: "Sales was 6,6% higher than...". I don't want to change the options in Excel to be different than system separator though. is it possible within Text function?
thank you,
Aga
Hi!
To convert a number to text in the correct format, use the TEXT function. Read this guide above.
I am trying to get excel to take a range of numbers and translate to text.
Said range:
SM = 0.0-3.9
MD = 4.0-7.9
LG = 8.0-100.0
I want to be able to enter a number (0.0-100.0) into a cell and it auto converts to the text with the corresponding range above. Example:
If I type 2.7 in the cell, it populates with SM
If I type 6.4 in the cell, it populates with MD
If I type 8.3 in the cell, it populates with LG
Please tell me there is a way to do this, I am at my wits end here...
Hi!
These questions have been asked many times already. The answer is in the first paragraph of this post: Excel Nested IF statements - examples, best practices and alternatives.
I have entered a date in cell B1 as 5/30/2022.
I have copied it to cell D2 using =$B$1 and formatted as yymmdd which displays 220530
Now I want to convert the displayed value 220530 to a six text characters that I can use in a subsequent concatenated string such as "="."&$B$3&D$2&"P"&$B10"
My best efforts using =TEXT returns "44711" not "220530"
Hi!
If you are converting a date to text using the TEXT function, be sure to use the date format pattern.
=TEXT(A1,"yymmdd")
This should solve your task.
Very much a novice, I have a cell with text and numbers . I want to convert the text to a + or - value. Using the letters U,D,R,L,F,B to indicate Up, Down, Right, Left, Front, Back.
Where letters U,B,R are expressed as a +ve value and
Letters D,L,F expressed as a -ve value.
Example cell value is B3. I want to convert value B3 to +3.
Cell value is R4. I want to convert it to +4
Cell value is L2. I want to convert it to -2 etc
Hi!
You can replace a letter with a number in a cell using a VBA macro.
Hi!
You can use the IF formula and then inside IF you can use RIGHT to select the numbers which are on right side. Using the conditions you can replace them.
Dear I want module equation for converting number to text. but not need decimal place conversion.
eg: USD 2,535.50 ( Two thousand Five hundred Thirty five Dollar and 50 cent only)
Hello!
We have a special tutorial on this. Please see Two best ways to convert numbers to words in Excel. Please meet the Spell Number add-in included with Ultimate Suite for Excel.
hi,
how to convert date of birth to text format in excel
11-01-2004 to Eleven January Two thousand four
Hello!
You can convert numbers to words using a VBA macro, for example, as described in this article.
In a cell it is reading '20.500, but I want it to read as 20.500 but text. I went to "Text to Column" to see if it has an ' to change, but it doesn't. How can I convert '20.500 to text in my cell?
Hi!
Have you tried the ways described in this blog post? This article provides 4 ways to write a number as text. They don't suit you? The formula
=TEXT(C1,"#.##0")
doesn't work either? Become familiar with the TEXT function and number formats.
Excellent ! I tried the "Text to column" and the function "text" to convert thousands of lines, both works fast and well. Now my lookups return the correct number. Thanks a lot !
The Text to Columns method works. Thanks!
The Format Cell to Text is not working any more; The number remains in a number format. Either a bug or a "bug by design".
Yup, same experience. Kinda annoying that Format Cell to Text doesn't work
Hi!
Format Cells option to convert number to text works for me.
this coding is not correct please end a correct coding to convert this :two thousand four hundred tirty five rupees only
this coding is not correct please end a correct coding to conver this no. 2435=two thousand four hundred tirty five rupees only
Thank you so much for this info... though I was going to have to mainly change 700+ entries, but the 'Text to Columns' option worked a charm for me!
Good day
Is it possible to convert such a delimited text of 00000304838 to be 3,048.38 in excel.
Thank you
Hello!
If your text is written in cell D1, then you can use the formula
=D1/100
and custom number format
#,##0.00
I hope I answered your question. If something is still unclear, please feel free to ask.
Thank you very much, stay blessed.
I was looking for a way to convert numbers into letters. So 1,2,3,4,5*,6 becomes A,B,C,D,E*,F (* is not multiply but a symbol of any type)
A bunch of numbers in one text box would become letters in another.
Hello!
To replace a number with a letter, you can use the CHOOSE function.
=CHOOSE(A1,"A","B","C","D")
I hope it’ll be helpful.
1: BEFORE you paste anything, open up a new page or highlight the cells you are pasting into.
(I find it best to use a new page)
2: Select ALL cells in the paste area, if you don't know how big your data is just click in the little grey triangle left of Column A and Above cell 1. It should select all cells on the page.
3: Right click and click "FORMAT CELLS"
4: In the format Cells dialog select TEXT - then click OK
5: Paste your data into the region you formatted as TEXT. (Adjust column widths)
Desired result is No more exponents! Good riddance, Excel is really garbage without a better way
to do this.
How to convert 92300640317 in word
Hello!
Please have a look at this article — Convert numbers to words in Excel
I hope this will help, otherwise please do not hesitate to contact me anytime.
I want to convert number above 96 to 96<, what formula should I use.
I need to set a column for phone numbers. I want them as text, but in a specific format, viz: 0404 505 393, that is four digits, a space, three digits, a space, three digits, but all as text so they are the same easy to read format.
Hi
How Can change dd mmm format to dd-mm-yy.
Hello!
Please check out this article to learn how to change date format and create custom formatting.
I hope it’ll be helpful.
Hi
I am currently working with international NGO in Afghanistan, and I wanted to excel to convert number in to text in the other cell, despite I have tried several times and followed different directions in result I would not be able to successfully overcome?
Hi!
If the advice in the article above doesn't work for you, try this guide: Two best ways to convert numbers to words in Excel.
How to convert num counting to alphabetic counting?
1. Select numbers, you want to sort.
2. From Data ribbon select Text to columns.
3. Click twice Next button.
4. On Column data format select Text and click Finish.
5. Now you can sort Numbers as Text.
To avoid confusion, please take out the period at the end of each line. For example, this seems to say the you will get the value of "123." for the first line when it fact the result is "123"
The result of =TEXT(123.25,"0") will be 123.
The result of =TEXT(123.25,"0.0") will be 123.3.
The result of =TEXT(123.25,"0.00") will be 123.25.
To keep the decimals only, use =TEXT(A2,"General").
Done but starting zeros of cell disappear in Already fed data. I need them for consolidation. Guide
Sir have a nice day. I have data in excel when I convert it in text form
It removes the starting zeros from that cell. I want those zero appearing. Because I need them for further consolidation. Please guide
Hello!
I’m sorry but your task is not entirely clear to me.
Please describe your problem in more detail.Include an example of the source data and the result you want to get.
Write an example, which zeros disappear in your data when you convert them to text?
It’ll help me understand your request better and find a solution for you. Thank you.
HOW TO FIND STRING OF NUMBERS IN RANGE FOR EXACT MATCH PLEASE?
12345 1 2 3 4 5 6 7
STRING FROM THE LEFT IN RANGE FROM THE RIGHT
TNAK YOU
Hello Mike!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
So many time I got pissed and wasted time trying many things because of that.
"Text to Columns" is very good thanks.
f in the chat, how to convert numbers to letters?
Excellent Post with multiple options. Thanks a lot, it resolved my problem.
I know this may sound crazy but is there a way to when I type in a number (for example, 6120) it changes to a word like necklace. I want to be able to type in a number of an inventory item and the next column it gives the name of that number. They will not always be in the same order, so it is possible? I have searched and searched the internet to see if it is even possible.
This is a very helpful article. The helper column + =TEXT formula + value paste is exactly what I've been looking for and this was an instant bookmark. Thanks!
Easiest way to convert 81039 dirhams.
please.
How to convert number to text as
50 :: Five zero
100 :: One Zero Zero
the 2nd option, changing the column to text *should* work, but idiot Excel has NEVER supported this, and continues to not support it. Example, put 1223123123 in the cell, format as text, and you will see the same reuslt: 1.22312E13 (Ok, I didn't count the digits, but EVERYONE LOVES Scientific notation, so much so that there is NOT freaking way to just select the column and say 'leave it the heck alone shit-for-brain's Microsoft' - been fighting this for decades now.
Your other options, making a column to convert the 'SKU' number that is 12 digits long to a Text might just work, I'll have to try that. I've in the past had to add a ' to the start of every dang line, and when there are 12,000 lines and all you freaking want to do is tell Excel to leave the dang thing Freaking ALONE, don't script leading 0's and are you kidding me with converting it to Scientific notation??
i need a excel code for spell number in digt format like.. 1 as one only, 10 as one zero, 0 as zero only..
Thank you so much.... great shortcut to convert Numbers to text...
Hi,
I want to convert number to word, when I enter a value it doesn't work and the following line of the VB program gets highlighted:
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2))
Please tell me what can I do to make it work?
Thank you.
Thank you, this was very useful
Save me a lot of time
Awesome very helpful
hi can you please help ma that how can i change 11/1/1950 into 11-50
For e.g.: in cell A1 - 11/1/1950
=TEXT(A1,"dd-yy")
Ans: 11-50
Hi,
want to add 20 numbers in A2(20numbers) & A3(20numbers) column and show the
result in A4
the result is accepting upto 15 numbers and remaining 5 numbers are displaying as 00000.
Thanks in Advance
Hi, need vba code for converting number to words ,have used spellnumber but I want the text only for number not for currency i.e 225 ( two hundred twenty five)please share with me
The last way: "Convert numbers to text in Excel with Text to Columns wizard" -- is the best!!!
Additionally, it can work in reverse and in other ways!!! Superb!!!
Congrats!!! & Thx a lot!!! Artur
Thank you very much for your feedback, Artur! Glad to hear you enjoy using our add-in :)
Best solution for this problem!!! AWESOME. Thank you!
How would you make a spreadsheet read a letter to a number as in if I type in just the letter x in a cell it would automatically change it to -40?
Tibor:
You would use an IF THEN statement which would look like this:
=IF(A2="X",-40,"Something Else")
Which means If the value in A2 is x, then display -40 otherwise display something else.
The details on how these statements work and examples can be found here on AbleBits.
Thank you Doug,
I see what you are saying but how can I convert any cell in column A to equal -40 when typing in x? What you described does change it but only for that one cell where the formula is entered
Tibor:
You can copy the formula down the column. The A2 address will change automatically as you copy it down the column.
Highlight the cell then click and grab the little black box in the lower right-hand corner of the cell and drag it down the column.
You have save me from going bonkers! So many people do not export the numbers as text but I do and to convert to a number is such a challenge in a large file.
The text to column wizard is perfect!
Thank you thank you thank you