How to convert all the numerical values to numbers from unknown data type?

Question

Hi,

My company's HR application generates late attendance reports in an Excel format. I need to write a few formulas to calculate penalties. However, the numerical values in the report are in some unknown data type. I am not able to write mathematical formulas, say SUM for the summation. If I click on the cell, press F2, and then press Enter, they turn into the proper number format and are summable.

Alternatively, when the cells are copied and pasted, the cells appear with green triangle marks. Clicking on the exclamation mark pops up the flyout menu, and from there it can be converted into numbers.

Alternatively, I can also select a column (only one column works at a time) and click "Text to Columns" in the "Data" tab. It converts into a proper number too.

My question is: is there a better way to convert the data into numbers?

The sample file is in Onedrive - Click here

Answer 1

The problem you are experiencing is due to the fact that the numbers have been entered as text. This is the same as if you had typed them in with a leading apostrophe.

Every place you use a reference to a cell which contains one of these strange numbers, such a I2 in your example, replace the reference with the function NUMBERVALUE, such as NUMBERVALUE(I2).

If you have an array reference, such as I2:I6, replace it with NUMBERVALUE function, such as NUMBERVALUE(I2:I6)

Using column H from your example, the formula =SUM(H2:H6) evaluates to 0. But the formula =SUM(NUMBERVALUE(H2:H6)) evaluates to -18 which I think is what you want.

Answer 2

Hi, Stay

How about this method?

put a conditional formula in a cell and converted it.

from your sample file,

copied and placed it in the same way.

Cell・・C10 = IF(ISERR(VALUE(C2)),C2,VALUE(C2))

autofill copy cells

C10　→　H10

　　　　　↓

　　　　H14

As a result, Sum is displayed.
I hope this helps.

Microsoft QA Login Difference Convert.pdf