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 4. Total comments: 213

  1. use below view for more clarity
    .................................... Total
    1 20 0 10 20 20
    2 25 25 20 10 25
    3 15 25 8 40
    4 40 200 10 30 40
    5 10 100 20 10 110
    6 30 100 6 20 30
    7 5 100 10 10 5
    8 0 100 8 20 0
    9 20 100 8 20
    10 25 200 0 30 25

  2. I have to get total of each row in below. I used "auto sum " to get the result, but it gets wrong value for each raw. How can i fix this, pls. help
    Total
    1 20 0 10 20 20
    2 25 25 20 10 25
    3 15 25 8 40
    4 40 200 10 30 40
    5 10 100 20 10 110
    6 30 100 6 20 30
    7 5 100 10 10 5
    8 0 100 8 20 0
    9 20 100 8 20
    10 25 200 0 30 25

    1. Hello!
      What formula are you using? Formula =SUM(A1:E5) doesn't work? Explain in detail what result you want to get.

  3. How to convert 89000478513C,890004687210 to 8900100047851303,8900100046871010 respectively and so on

    1. Hello!
      To replace a letter with its number in the alphabet, use the formula

      =CONCAT(IFERROR(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1), IF(CODE(MID(A1,ROW($1:$93),1))-64<=0, MID(A1,ROW($1:$93),1), CODE(MID(A1,ROW($1:$93),1))-64)),""),"")

      This is the formula for uppercase letters. If you need to replace lowercase letters, then instead of 64 use 96 in the formula

  4. how to convert area to numbers
    exp.area= 79.84 convert into number?
    please explain

  5. Hi,
    How do I recode a drop-down list in Excel, in other words assign a code number for each of the drop down responses? I've tried several of the options but they haven't worked.
    Thanks

    1. Hello Ellen!
      You can use the Combo Box (menu Developer - Insert - Form Controls - Combo Box). It returns the number of the selected item from the drop-down list to the cell you designated

  6. I have
    AA 8881/17
    BR 078/16
    BR 078/18
    BR 078/19
    CZ 458/18
    EK 148/15
    EK 148/15
    EK 9287/18
    KL 0427/15
    KL 0427/18
    and i need only the last 2 digits or to be sorted from the last 2 digits how can I do it?

    1. Hello!
      To extract the last 2 digits from the text, use the RIGHT function
      Text Result -

      =RIGHT(A1,2)

      Number result

      =--RIGHT(A1,2)

      I hope it’ll be helpful.

  7. How we can data in number when some data copied with table and pasted in excel now that numbers are in text and before starting numbers many spaces are there
    how we can do convert such cases in number format

  8. Hi,
    I cannot use vlookup formula when numbers are presented as text.
    Example I have:
    0456
    1247
    1234/1,
    and I need to have the values as text, so I can sort them A to Z. I always get the error massage, when use vlookup formula.
    Any help?

  9. I have a column on the left that goes from 0-60. The column on the right contains a formula that reads: =COUNTIF(CY$4:CY$66,0
    I put $ so that those inputs don't change but I'd like for the 0 to change to 1, 2 and so on.
    So going down the column it should look like this:
    =COUNTIF(CY$4:CY$66,0
    =COUNTIF(CY$4:CY$66,1
    =COUNTIF(CY$4:CY$66,2
    =COUNTIF(CY$4:CY$66,3
    And so on.
    That way I don't have to go into each cell to change each number individually. The reason I have to change it is because I have to add and delete rows which causes for there to be a gap which I then have to fix. I'm sure there's a way. Please help!

    1. I recommend using the ROW function as a counter.
      If your formula

      =COUNTIF(CY$4:CY$66,0)

      is in cell D2 (i.e., line 2) then change the formula

      =COUNTIF(CY$4:CY$66,ROW()-2)

      After that you can copy this formula down along the column.
      Hope this is what you need.

  10. Hi all.
    Need help on formula.
    E.g.
    In cell A2, there is 4 digit no. "1234"; cell A3, "2345".
    In cell B2, there is a text "M" and cell B3 text "C".
    How to form a formula in cell C2 and C3 that the output are "MM-181234-01" and "CC-182345-01" respectively.

    1. Hello Judith!
      If I got you right, the formula below will help you with your task:

      =CONCATENATE(B2,B2,"-18",A2,"-01")

      Hope this is what you need.

  11. I have a number 328,345 in General format
    Somehow I looked some of the hidden characters it looks like "328,345 " notice the space
    I used clean, trim and value in all the nine orders but to no avail can i get that number to allow me to do any number functions like add.
    Any ideas

    1. Hello Fran!
      If I understand your task correctly, the following formula should work for you:

      =VALUE(SUBSTITUTE(A1,",",""))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  12. Please, kindly suggest how to convert all text strings to numbers in a column in Excel sheet? Example:
    $3,51,670
    $4,24,980
    $2,31,650
    $5,29,810
    $3,12,980
    $2,47,360
    $9,23,840
    $1,26,120.
    I have tried all the suggestions stated above but it still doesn't recognize it as a number when I want to calculate or find the maximum of some numbers and it is included.

    1. Hello!
      If I understand your task correctly, the following formula should work for you

      =--SUBSTITUTE(SUBSTITUTE(C21,",",""),"$","")

      I hope this will help

  13. Hello,
    I'm working with Geodesy and obviously excel is a part of it since i insert my points in measurement device. So in one of the columns i have list of numbers 145,987 etc. and it appears to be correctly written number, until i select that cell and in the function bar it appears 145987. I've tried changeing to text, customize format cells general, even entered formulas, and in options changed that thousands be marked as , and I manage to change number with a comma 145,987 but in function bar it says 145987.

    Could you please help me.
    Thank you,
    Regards!

    1. Hello Enisa!
      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.
      What exactly do you enter in the cell - 145987 or 145,987 or 145 987? Do you want to change cell format (appearance) or cell content?
      It’ll help me understand it better and find a solution for you. Thank you.

  14. Hey everyone anyone help me my some project. How a number replace in to next column.
    Column 1 1234567
    Column 2 Your Sr xxxxxxx will be closed against column 1 number.i want to show a number in column 2 where xxxxx will be shown.
    Which formula used in excel

    1. Hello Jay!
      If I understand your task correctly, the following formula should work for you:

      ="Your Sr xxxxxxx will be closed against "&A1&" number"

      or

      =CONCATENATE("Your Sr xxxxxxx will be closed against ",A1," number")

      I hope it’ll be helpful.

  15. None of this optinons worked for me. What a waste of time. FY

    1. Hello!
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  16. Great article, very technical, detailed and simple all at once.

  17. IMPORTANT!!! Please, review the method for converting Text to Numbers ("Change text to number with Paste Special"). I believe your instruction "Paste Special" should be followed by "Formats" and not by "Values". Otherwise the existing data in the selected cells will be lost....
    With all due respect,
    Keep up the good work,
    Michael

    1. Hello Michael,

      Thank you for your feedback. The instructions are correct. The idea is to convert numbers stored as text to numbers by performing an arithmetic operation, addition in this case. For this, you add an empty cell (i.e. zero) to the copied Values, not Formats.

  18. How do I convert a number stored as text (1 hour 30 minutes 45 seconds) to time format (01:30:45) in Excel?

    1. Hello Angela!
      Please try the following formula:

      =TIME(LEFT(A1, SEARCH(" ", A1)-1), MID(A1, SEARCH(" ", A1, 3)+1, SEARCH(" ", A1, SEARCH(" ", A1, 3)+1)-SEARCH(" ", A1, 3)),MID(A1, SEARCH(" ", A1, 15)+1, SEARCH(" ", A1, SEARCH(" ", A1, 15)+1)-SEARCH(" ", A1, 15)))

  19. Hi,
    New to excel, but wondering if there is a way to convert a place value for a point score (sporting event spreadsheet) across multiple cells
    Eg. 1st place(1) = 4 points, 2nd = 3, 3rd =2 & 4th =1
    So that when you type 1 it automatically converts to 4 etc
    Thanks

  20. Thanks for the tips, they were very helpful, easy to follow and you provided more than one way to complete the task at hand. Big KUDOS to you

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  37. Thanks Svetlana. Nicely written and organized.

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

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

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

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

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

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

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

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

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

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

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

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

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

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