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 2. Total comments: 144

  1. This has been such a mightmare. I finally got the following to work. Following all your suggestions and more, I just replace-all'd elimination of "$" and "." and ".00" to get 2, 3, 4, and 5 digit numbers that stubbornly refused to go to numeric. I extracted from the left the correct number of digits -- eg, 50 wants =let(cell,2) -- then I had to =trim(new cell) **even though there is no apparent space anywhere in sight**. Still it's not numeric but at that point you can multiply by 1 and turn it into a freaking number: yay!!!

    You cannot imagine how long it has taken to get this to work.

    And you have to manually reset the number of digits to extract. extracting more than the visible digits and then triming, just doesn't work. (@@) go figure.

    1. Did you manage to resolve this? I am having the same issue here.

  2. Hello, I'm trying to erase a space before a number (for thousands of cells which I got from another file) but I can't do it:
    Example: a cell contains a space before a number 1, and I need it to be just number 1, without a space before it.
    The problem with the number 1 after a space is that excel consider it a a text, the SUM of it is 0.
    When I overwrite a 1 in that cell which contains a space before the number 1, it converts to a number and SUM operates normally; but I can't do it cell by cell for thousands of cells.
    Please help me.

    1. Hello Tommy,
      Please try the TRIM function. (TDLR, google how to use TRIM)
      For example, if the column containing the numbers is A, and the first cell with data is A2 [not counting the header row], you should:
      1. Create (Insert a new column (B in our example)
      2 . In column B2, type: TRIM(A2)
      [this will give the output of the content of the content of cell A2 without any leading or trailing spaces]
      3. You can then fill the formula down by double-clicking the fill handle or any other way.
      [this will make the entire column B the same as A without any leading spaces]
      4. You can then copy column B and paste it in A as VALUES using the PASTE SPECIAL.

      I hope this helps.

    2. Do a 'find and replace' and put a space character in the find field and nothing in the replace field. It will apply to everything though....

  3. Hello,
    I am trying to convert Satisfaction surveys from text to numbers, to avoid manual converting for receiving a high number of surveys in a weekly. I was provided with the following formula with little to no information on how to add it on the exported repot. There is a main sheet, but the data needs to be add once the exported sheet is completed. The formula provided is below and I am trying to convert only one column from text to numbers.
    =IF(T3489="Extremely dissatisfied",1,IF(T3489="Somewhat dissatisfied",2,IF(T3489="Neither satisfied nor dissatisfied",3,IF(T3489="Somewhat satisfied",4,IF(T3489="Extremely satisfied",5,0)))))

    Hoping to hear back!

    1. I think the above formula is just working fine & is able yo convert the col containing the survey response to numbers. What is the exact issue, unable to comprehend from your message above.

  4. 14811.10
    10363.44
    1 059.00
    -4 534.00
    -10 104.90

    How to get this into numbers ? Text to Column doesnt work

    1. Hello!
      Please try the following formula:

      =--SUBSTITUTE(A1," ","")

      If you use comma as decimal separator then use this formula

      =--SUBSTITUTE(SUBSTITUTE(A1,".",",")," ","")

      I'd recommend you to have a look at our Convert Text tool that can help you convert numbers stored as text to numbers. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  5. How do I convert a duration to show a number:
    example:
    0:07
    0:15
    0:35
    And convert these to read as:
    0,07
    0,15
    0,35

  6. I have non numerical data (responded in monkey survey) the responses are in word i.e. agree, strongly agree, disagree, strongly disagree for many questions. I have exported the data of the survey to Excel sheet and all the responses are in word (string). I need to analyses basic statics corelating it with one variable (like male/female respondents). How do I convert this strings to numeric for analysis in excel?.

      1. Thank you Alexander, Very helpful

      2. @Alexander Trifuntov - Thank you very much, this has been very helpful and work simplified. Thank you very much once again.

  7. Hello
    I have
    1819VJO2045643_1
    2021RDO456326_1
    1920TDO785432_2
    Etc
    Etc

    Like this data with me i want to convert this string in to number or unique numbers how to convert please help me
    Thanks

    1. Hello!
      To extract all numbers from text, try this formula:

      =CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$94),1)),MID(A1,ROW($1:$94),1),""))

      If this is not what you wanted, please describe the problem in more detail.

      1. Sir
        how to find numbers and text

        Expl

        sunil12542kumar

        I WANT RESULT

        Sunil KUMAR 12542

  8. Hi,
    I am trying to convert a value with dollar sign in text to Numeric but Value Function is giving error instead of giving 400 & 500 as results. Excel doc also says this should work. What may be the probable reasons.
    I am posting the exact code below

    A B C
    1. Name Money Won Value
    2.
    3. Mico $400 #VALUE! #=VALUE(B3)
    4. Noah $500 #VALUE! #=VALUE(B4)

    1. Hi!
      What are you trying to convert to a number, Mico $400 or $400? What formula are you using in cell B3? If you are trying to convert the value "$400" to a number, check which currency is set by default in the regional settings of Windows.

  9. Dear all, thank you for all these tips but it is not working for me. I need to change numbers stored as text into general format in order to be able to use a VLOOKUP formula. When I change my text into general I notice that the value change as well. For instance, it changes 92753660758293 into 92753660758290... How can I keep the right value? Many thanks for your answer.

    1. Hello!
      I don't know how you turn text into numbers. There are many ways. I have not been able to replicate your problem. However, keep in mind that the maximum number of numeric digits in Excel is 15.

  10. A1= 1
    A2 = (1+5)/2 as text
    A3 = (2+4)/2 as text
    in A4 i need the formulated A1*A2*A3
    A2 & A3 only can be write as text type

    1. I have same issue

  11. I have a large file with a column of data where I want to convert a 5 digit/character code alphanumeric text to number. Your instruction handles most of these cells in my file, but not for instances with an embedded alpha "E" character in the string. This column has 5-character codes that I wish to convert to number for a vlookup application. An example: a code of 71E62. I cannot get that cell (and many others) to convert to a number.

  12. I am still battling with this:
    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.

    It works !!! - until the "text number" is in 1000's
    ie: R35,06 text converts to R35.06 number and
    R128,71 text converts to R128.71 number

    BUT -
    R4 020,40 text says as is and does not convert

    1. Hello!
      You can remove spaces from text using the formula

      =SUBSTITUTE(SUBSTITUTE(B1," ",""),",",".")

      I hope it’ll be helpful.

  13. Hello All,

    I have data with 10 digit followed by X. I need a digit in place of X.

    Example: 0123456789X is the data and i need the output to be like 01234567890, 01234567891,.......0123456789

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

      ="0123456789"&SEQUENCE(100,1,0,1)

      You can learn more about SEQUENCE function in Excel in this article on our blog

      I hope it’ll be helpful.

  14. how do I convert all text strings to numbers in a column in Excel sheet
    'Php200.00
    'Php500.00
    'Php200.00
    'Php100.00
    'Php3,432.00
    Thank you.

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

      =--MID(SUBSTITUTE(A1,",",""),4,20)

      Hope this is what you need.

  15. none of these worked for me. I have just numbers copied from powerpoint into excel. I've used, trim, value, left, text to columns, and just simply changing it, and none of these are working.

  16. 39.2 -90.1 I have two values but i keep to both values in two different column how to do?

    1. Hi,
      Please check out this article to learn how to split text string by delimiter. Then you can convert two text strings to a numbers as described in this tutorial above.

  17. I have a column of numbers that have an "AUD" reference at the end of each number. As a result it is not recognising them as numbers and I cannot tally the column. How do I remove the "AUD" reference from each cell to enable it to be added as numbers?

    1. Hello!
      If I understand your task correctly, you can use SUBSTITUTE function to remove unnecessary text and extract number:

      =--SUBSTITUTE(A10,"AUD","")

      If you have other questions, don't hesitate to ask.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)