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!
169 comments
Hi thanks for the solution it saved so much of time for all my colleagues when we are working with excel.
i would like to know how can i get day to day update of a particular company share price in excel by using a formula from goggle finance or money control.
Thank You. This Helped.
Hi thanks for the tips. They are very helpful.
But my issue is that I have few numbers in which I need to add some zeros before them, and then convert to text.
For eg. 1234567 in number needs to be changed to 001234567 in text. I have a string of thousands of numbers like this. Kindly let me know if there is any shorcut in excel for doing this. Presently I use concatenate.
is it possible to use a formula or conditional formatting to convert a number value into a text string. For example: I have a series of tasks assigned numbers 1 - 5. I'd like to have a text value display in the next column that if A1=1 then B1 would contain the text "submitted" and if A2 = 2 then B2 would contain the text "in progress", A3= 3, B2=support ticket issued... is this possible?
Hi,
from what you described, I believe you may find the IF function useful. Please take a look at this article to learn how you can apply it to your table.
Hope this helps!
Sorry Just figured out the solution was Text To Columns as posted in above article. Got the answer.
I have a large chunk of data directly extracted from SQL database where a column was previously VARCHAR[20]
This one column called NationalID randomly shows correct values and values converted to Short Format e.g. 2.55122E+11
Regular/Correct value format : 255122100995
Short/Invalid value format : 2.55122E+11
When I double click on the cell it shows me the
correct value 255122100995 instead of invalid value 2.55122E+11
I tried cloning it the the next blank column and converting it to text but still it shows the value only after double click. Since I have lakhs of rows to process I need a formulatory way out
Am a programmer so not much aware about how to go about this excel issue.
Kindly guide.
I want to convert costs into a letter code. Currently we use a system where every number 0-9 has a specific letter.
So, if the cost is 108344, that translates to a code of PXHRKK because the numbers above translate to the letters below:
1234567890
PARKEFGHIX
How can I automate this translation in Excel?
Hi,
I have a column of 500 numbers, (1-25) in a column. Each number, 1-25 represents an industry.
Aerospace and Defense = 1
Automotive = 2
Banking = 4
I need to replace the numbers, 1-25, with the corresponding industry match.
How do I do this?
Thank you so much for this. You saved my day. Now I can have cash and go to Spar.....Lol
Copy paste from word Actual Figers
1.11205E+15 1112053000152279
2.10705E+15 2107053000163925
5.10105E+15 5101053000292829
4.10405E+15 4104053000607538
9.99902E+12 9999024321252
3.11005E+15 3110053000081445
4.10305E+15 4103053000342622
1.21305E+15 1213053000146229
4.11705E+15 4117053000118075
2.10105E+15 2101053000222052
2.21005E+15 2210053000247300
2.11105E+15 2111053000194473
2.21205E+15 2212053000177567
1.12205E+15 1122053000179723
2.11005E+15 2110053000240877
how can i Solve this issue,
Im copying this figers PDF to Word then I'm copy this figers from word to Excel
This article makes Excel bright. Thank you Maria.
Thanks very much. This saved me a lot of time as all the other conversion methods did not work for some reason :-) Happy New Year!
Thanks
i want this thing in ms-excel
when i type 225 in a cell than two hundred and twenty five should come in other cell.
thanking you so much for this formulas
Hi all
i'm creating xls doc using openofficexml epplus
i adding values in xls using ws.Cells("D1").value = 1234
i would like to get something like simple quote '1234 as description in "Add an apostrophe to change number to text format" with result 1234 in cell but '1234 in text zone
any idea ? is it a specific value ? formaula ?
thx
can any body guide me how to fix the specific code for tex/name in excel sheet on typing the name we see the result of specified code in required column.
for example when i type "apple" and its code (100) appear in the required cell.
Hi ramzan,
Please show us how your data looks like.
hi
How find the length of string For example in cell A1 Dilip sai001 is there so i want count for alphabets only ,if we use Len () means it will count space and numbers also So can u help me
Hi Dilip,
You should use the following formula:
=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},"")))
hello madam,
how to fixed excel sheet in value convert in word .
How to display a numeric value and a string in a excel cell ?