Comments on: How to convert numbers to text using Excel TEXT function and other options

This tutorial shows how to convert number to text in Excel 2016 - 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. Continue reading →

Comments page 2. Total comments: 169

  1. 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?

    1. 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.

  2. 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 !

  3. 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".

    1. Yup, same experience. Kinda annoying that Format Cell to Text doesn't work

  4. this coding is not correct please end a correct coding to convert this :two thousand four hundred tirty five rupees only

  5. this coding is not correct please end a correct coding to conver this no. 2435=two thousand four hundred tirty five rupees only

  6. 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!

  7. Good day

    Is it possible to convert such a delimited text of 00000304838 to be 3,048.38 in excel.

    Thank you

      1. Thank you very much, stay blessed.

  8. 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.

  9. 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.

  10. How to convert 92300640317 in word

  11. I want to convert number above 96 to 96<, what formula should I use.

  12. 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.

  13. Hi
    How Can change dd mmm format to dd-mm-yy.

      1. 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?

  14. How to convert num counting to alphabetic counting?

    1. 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.

  15. 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").

  16. Done but starting zeros of cell disappear in Already fed data. I need them for consolidation. Guide

  17. 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

    1. 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.

  18. 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

    1. 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.

  19. So many time I got pissed and wasted time trying many things because of that.
    "Text to Columns" is very good thanks.

  20. f in the chat, how to convert numbers to letters?

  21. Excellent Post with multiple options. Thanks a lot, it resolved my problem.

  22. 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.

  23. 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!

  24. Easiest way to convert 81039 dirhams.
    please.

  25. How to convert number to text as
    50 :: Five zero
    100 :: One Zero Zero

  26. 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??

  27. i need a excel code for spell number in digt format like.. 1 as one only, 10 as one zero, 0 as zero only..

  28. Thank you so much.... great shortcut to convert Numbers to text...

  29. 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.

  30. Thank you, this was very useful
    Save me a lot of time

  31. Awesome very helpful

  32. hi can you please help ma that how can i change 11/1/1950 into 11-50

    1. For e.g.: in cell A1 - 11/1/1950
      =TEXT(A1,"dd-yy")
      Ans: 11-50

  33. 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

  34. 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

  35. 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

    1. Best solution for this problem!!! AWESOME. Thank you!

    2. Thank you very much for your feedback, Artur! Glad to hear you enjoy using our add-in :)

  36. 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?

    1. 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.

      1. 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

        1. 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.

  37. 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

  38. I'm trying to link a cell that has a name in it to another sheet. The cell is actually words, not numbers, but I get either the formula or the number zero as if the cell is reading a formula instead of the words.

    I've tried your solutions to convert numbers to text and it did not give me the name, only 0 or the formula.

    Can you help?

    1. first select the cell where you want to get the copied name type = and traverse to the sheet where the name is already present, click on that cell and press ENTER. drag the cell to get other names

  39. Glad I happened upon this Text to Columns solution to this issue! As a Microsoft Partner, please convey to Microsoft that the Text to Columns method is how the standard Format as Text should work!

    For years I've been struggling with the best way to deal with the broken built-in method. I started teaching Excel usage over 25 years ago and have had a career of programming and I can't remember when this issue cropped up. It plagues all areas of data manipulation and transfer into and our of databases where Excel is used as an intermediary--suddenly Identification numbers are corrupted as Scientific Notation versions of the underlying string of numbers. I don't know why anyone, anywhere, would ever want numbers stored in a column along with text used solely as identifiers and not values, to suddenly switch to Scientific Notation when changing to text...if anything, the numeric display should be Scientific Notation if proper and reformatting a column to Text would automatically convert to the original string of numbers.

    There should at least be some simple setting that the experts can make to make this bizarre action take place for those that think it has value and let the other 99.99% of the world be able to use Excel without tripping over this.

    Thanks for listening...I hope you have a voice they'll hear.

    Adam

  40. There is a much easier and faster way to convert a column of numbers in text format to number format by using the data - text to columns function.

    Suppose your column contains a number like this:

    1'235'612.47

    It will be recognised as "text" due to the apostrohes. Same thing if the number contains commas, like this:

    1,235,612.47

    All you need to do is click of the column, then click the data tab and the text to columns tab. Choose "deliminted". Un-check all the delimiters, like Tab, Semicolon, Comma, Space and so on. Click Next, then Advanced. In the boxes which appears, specify the thousands seperator which is being used by the text number. i.e. an apostrophe in the first case or a comma in the second. Click OK and Finish and all numbers are now in number format.

  41. Hi,thank you very much for your the sharing. I have a case here which cannot be addressed by any of the methods above. I create a pivot and use "customer ID"(which is already transferred to text in backend data) as a pivot filter. But when I use the filter to vlookup the customer name(so that I can get the corresponding customer name when filter changes), it failed. I guess it's still due to the cell format but wonder how to fix it by using a formula. "=text(**,"@") " doesn't work.

  42. Can you plz give me a solution for this
    Eg: What(4) do(2) you(3) want(4)

  44. I need the formula of one value i put in one colums same value i need in another colums . ie. 1275 in one colums another colum one thousand two hundred seventy five only. Pls give me the formula.

  45. I need to know how to input numerical data into a column and have it converted to word text. (Example.....if I type in 3, i want excel to convert it to the color white.) What formula or method do I need to use.

  46. How to convert numeric value ???

  47. This didn't work for me. I've tried absolutely everything and I cannot convert the numbers to stored text.

  48. 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.

  49. Thank You. This Helped.

  50. 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.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)