You must Sign In to post a response.
  • Category: Software & Apps

    Data in excel not converting to numbers

    Is your data in Excel getting converted to exponential format? Know from our experts how to prevent this from happening.

    I have copy-pasted some data from a word file to excel sheet. In the word file, there was some data in tables, which I copied and pasted in excel too. Now the data in table from Word file in excel are not working.
    I tried converting them to numbers by going into format option but, only a few data in cells converted to numbers but rest remained as it is .i.e. not converting to numbers.

    When I add the cells the results are the addition of a few cells only and not all. Those cells that are not converted to numbers do not add up during the addition of cells.

    I even removed all formatting from the excel sheet but the problem still persisted.

    Any help would be welcome as I don't want to type each number manually. Yes, the content of the cell gets converted when I type manually but not otherwise.
  • Answers

    1 Answers found.
  • Sometimes excel stores numbers as texts, usually while using SUM and Average functions. Let's check the two main reasons behind this.

    If you use any symbol like a comma or apostrophe before a number, usually if you have downloaded the data from a database or from any unsupported file. and the symbol may not be visible, but it will still exist.
    If the cell has a formula like LEFT, RIGHT

    Solutions are as below:
    1. Convert to the number option: go to Excel, select the desired cells, go to the home tab, select numbers drop-down, select general. Now all text will get converted into numbers.

    2. Use paste special option: enter any number in an empty cell, copy that cell, select the affected cell, click on paste option drop-down, click paste special.
    Use TRIM, CLEAN formula: the formula is =VALUE(TRIM(CLEAN(A1)))

    In addition, you can use the "text to number" and "text to columns" options also.

  • Sign In to post your comments