Comments on: How to convert text to number in Excel

Sometimes values in your Excel worksheets look like numbers, but they don't add up, don't multiply and produce errors in formulas. A common reason for this is numbers formatted as text. This tutorial will teach you how to convert numeric strings to actual numbers. Continue reading →

Comments page 5. Total comments: 213

  1. if i convert 3108101010000191 text in to numeric by using value function, answer comes to 3108101010000190 why?

  2. 1,19,583
    87,61,525
    I am unable to convert these into numbers as well as unable to sum of them. Kindly give solution.

  3. how to convert 2,66,020 into text

  4. How to rectify the below scenario?
    Offshore team, excel format in column A is populated as text, but when onshore opens the sent excel, in their machine the column A appears to be in Number format.

  5. your tip really helped me on how to change text number to number, as I have been on it for days, i initially thought my excel wasn't working. This has been helpful. Thanks

  6. How do you convert a text of .01- to a number -.01?

    1. Nevermind i figured it out, sorry to bother you

  7. I want to separate this number in 2 columns, breaking it where the comma is...

    "25,10"

    The result I want to obtain is 25 and 10. But when I convert it I get 25 and 1.
    what am I doing wrong?

    Best,
    PM

    1. Select the cell u wanted to convert>Data>Text to columns>select delimited and click next>select comma from the delimiters column and click on finish.

  8. I have pasted a large number of values which have "." as a separator (example 23.310
    ). I need to convert this to either "," (example 23,310) or no separator at all. I have tried formatting the cell to currency, general and few other just to get a value to do calculations and it doesn't work. Is there a command which would replace all "." to ","?

    Thanks in advance.

    1. Hi Marcin.
      Same problem here. Some logs have 23.310 instead of 23,310 and import them to excel doesn't change the format.

      A very quick solution that worked for me:
      - Choose the column with mouse
      - Use replace function -> replace . for , -> it's text so it's works with no problem
      - If necessary format the column to number after it replacing

  9. I want to change the text into number by following way:
    abc = (1+2+3) = 6
    bcd = (2+3+4) = 9
    cde = (3+4+5) = 12

    Pls Help. Thank You in Advance.

  10. How to create a sum field that totals columns that were converted to numeric via Data >Tools> Text to Columns. Sum function not recognizing values in columns converted as numeric, even with format changed to numeric.

  11. In order to use Regression in the data analysis I need to have numeric data. However some of it is blank using "" in a previous formula, but this is recognised as text. Do you have any suggestions as to what I can put instead of "" which represents blank but is not text?

    NULL would also be text of course...

  12. Is there a way to do the following: I have a row and in it, it has Single and Family mixed all the way through. I want it to basically calculate how many Single cells there are but a single cell is a certain cost like $50. The same with Family, how many family but the price for family is $100. So it won't actually tell me there are 4 single and 8 family, it would give me the total price tag.

  13. '0200009782563
    0200009782563
    Both cell len show 13. How I found out both cell data or len different?

  14. How do i change from a qualitative data (Beginning/Developing/Achieving/Exceeding) to a quantitative data (1/2/3/4pts) when a person key in this in google excel:
    Beginning = 1pt
    Developing = 2pts
    Achieving = 3pts
    Exceeding = 4pts

  15. Can I formulate a word, example Estate to equal 30 days so in the spread sheet when the word estate is used it will calculate 30 days from a specific date in a cell?

  16. So, I have a form created to enter data and the form is linked to excel. The issue I am having is that when someone enters the amount on the form it gets saved as text on Excel. This form is an ongoing process so is there a way to automate the Excel to convert all entries in the Amount column to Number? Instead of converting them everytime manually?

  17. Thanks Svetlana. Nicely written and organized.

  18. I want ton convert the following to from the following format
    Fri Aug 23 13:05: 39 2019 to dd/mm/yyyy format Is this possible??

  19. I have the following challenge, when generating a string that I'm then willing to reference a named variable. Imagine 4 cells in a sheet, such as:
    A1 contains the number 9, and I name that cell Blue.Color using Formula/Define Name
    A2 contains the number 6, and I name that cell Green.Color
    B1 contains the text Green
    B2 contains a formula such as =A2&".Color"
    The problem is that B2 cell now shows Green.Color, whereas I would have wanted to read 6
    Any trick for me ?
    Thanks a lot,
    Sebplus.

  20. Select text which is to be converted in number
    prss Alt DEF

  21. the value of A1 is "31-35", how can I convert this to (4)? When I use =Value(a1) the result is #value!

  22. A5 consists of a set of values "3,8,5,6,12" collected from other formulas.
    This set of values will have to be used by CHOOSE frequently, CHOOSE(x,A5) where x is a variable.
    But, "CHOOSE(x,A5)" returns "#VALUE!".
    How can I make it work, please? Thank you very much.

  23. Finally, Someone who actually knows what they are doing. All of the articles I found including MS only give you one or two options. The Paste Special function is the first time I've seen this method and it did the trick for me. Great article, well organized, comprehensive, and easy to follow. Thank you

  24. How to convert numbers to word.
    Exp. 1 - A
    2 - B
    3 - C

  25. Hi,
    I want to convert a placement to an other number, like: placement 1 = 10, placement 2-5 = 7.
    What is the best way to do this?
    thanks in advantage!

  26. Hi
    How to convert a value number of 3.5581E +14 to the right number. it is not working in with format cells. Thanks for responding.

    1. Just paste the number by adding " ' (Appostropy) ". exp 123456789 should mention as '123456789

  27. I need to give the letter P (for present) the value of 1 and the letter A (for absent) the value of 0. Very new to Excel and can't figure it out. Thank you.

    1. Use the Excel function IF.

      1. Hi, Just Select all, then Ctrl+F, at top select "Replace", then in find what give "1", and give "P" in Replace with and right below select "options " and select only "match entire cell content" and select "Replace all" in left bottom

  28. Kindly suggest how to convert all text strings to numbers in a column in Excel sheet? Example:
    2,66,020
    2,66,020
    2,24,650
    70,170
    91,250
    2,66,810
    2,66,810
    91,250
    2,66,810
    2,64,840
    2,67,720
    17,150
    2,67,720
    2,360

    1. Hi,
      It seems to me that the easiest solution is to apply the ‘Extract’ tool offered by ‘Ablebits Ultimate Suite’. One of the options its menu has is ‘Extract numbers’. Simply click the corresponding radio button on selecting your records, and a new column will appear next to the original one. The extracted data will get numerical status for Excel and lose commas, by the way.

  29. I need help making a number in D5 which is 1 to mean 2 in the formula I used in P5.
    =(A5/C5)*D5+A5+40

  30. Kindly suggest how to convert all text strings to numbers in a column in Excel sheet? Example:
    2,66,020
    2,66,020
    2,24,650
    70,170
    91,250
    2,66,810
    2,66,810
    91,250
    2,66,810
    2,64,840
    2,67,720
    17,150
    2,67,720
    2,360
    Thanks. RAKESH

    1. just replace , with blank and its done :)

  31. You guys are making my life easy...
    Thanks a lot for your support

  32. The other problem might be that the 'System Separator' is not the same as your pasted value.
    I.e. In the cell you have value 100.22 In other words 100 point 22 or 100 decimal 22.
    If your File -> Options -> Advanced -> (About 16 lines down) Use system separator is ticked then the decimal point will make the field text - No matter what you do.
    To correct this, un-tick 'Use system separators' and ensure that 'Decimal separator' is a . and 'Thousands separator' is empty and not a blank.

    1. Thousand separator is taking a , automatically even if we delete it.

  33. How to convert 1911420211989 this number in word

  34. Thank you gurus of excel!

  35. Thank you!

  36. Thank you so much to this very helpful post.

  37. ShortCut key - ALT+a+e+f

    1. Thanks to you, column to text was causing excel to crash, this was the best solution since i am trying to automate the a script, that does just this.

  38. Hi

    My excel does not recognize the following as a value 2 027,60. I need a thousands separator to be a "," and decimal to be "." . I have a whole column of values that need to be converted.

    Please help!!!

    I have tried the substitute function which works on changing the decimal function for values less than a thousand, thereafter it does not work on a value of 1,000 or more.

    Thanks!

  39. There are a number of tricks you can use if you have a column of numbers in a format that Excel does not recognise as numbers. For example, let's say you have a column of numbers where some idiot has typed each number in using a comma as the thousands separator like this:

    52,321.45
    17,615.28
    19,785.98
    41,963.49

    If your version of Excel does not use commas for the thousands separator, (mine is et to use the apostrophe - ' -), or if some dummy actually typed in the commas, you can still quickly convert the entire column of text-numbers to numbers by using the Data - Text to columns function:

    Highlight the entire column.
    Click text to numbers
    Select "Delimited" and hit next.
    Uncheck all the delimiters and hit next
    Click General and then Advanced
    In the advanced box it asks for the decimals separator and for the thousands separator.
    Enter the separator you see on the screen (NOT the separator that you want). So in the above example you would put a comma as the thousands separator, and a full-stop as the decimal separator.
    click OK and finish.

    Hey presto your text-numbers now ARE numbers in your preferred Excel format. (with an apostrophe as the thousands separator - in my case)

    1. Excellent! Simple and clear, and fully efficient. Thank you!

  40. Saved my day too..

  41. You are amazing! Saved my day and future days as well!!

  42. How to change a text into the number like we need to convert "Best" is equal to 5.

    1. In case if we have a column with 100 text cells and each of them have to be converted into unique numbers, it will be cumbersome to use nested IF function, what will be the best option?

    2. =IF(A1="Best"),5,""). The number 5 may be associated with a certain cell.

      1. Hi,

        I'm busy doing a spread sheet with all my golf scores and related points for 2020/2021.

        Example: 65 equals 36 points or 70 equals 30 points.

        My question is how can I formularise this so that if I type 65 it will automatically insert 36 in the next cell ?

        Thanks

  43. I want to add text data field in rows portion after Values field in excel pivot table. How can I do that?

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 :)