Excelling not summing as expected.

[roxsingh]

Hello,

A report was done using BI Publisher and exported to excel. However, when exported, the values appear as text. I converted it to number by multiplying by one however, when i do an auto sum i am not getting the expected result as the sum.
Can you tell why this is so and what can be done to rectify the issue.
Please see attached.

[Sam Capricci]

where did you do the multiplication?
Starting in B2 I put in =A2*1 and copied down and the two column totals match.

[roxsingh]

where did you do the multiplication?
Starting in B2 I put in =A2*1 and copied down and the two column totals match.

Hello,
I entered 1 in a blank column and use the space paste special option and multiplied. I tried what you did and i am getting '#VALUE!' appearing in some fields (see column B in attachment) as some is not converted to number. The actual sheet have several columns that i would want the fields to properly convert to number as i am going to copy this sheet as the master sheet on another workbook. How can i achieve this?

[Roel Jongman]

Hi roxsingh,

The trick of multiplying by one is not working well because you also have 1000 separators in the numbers. this makes your conversion trick fail
Another (fast) trick to convert back to numbers is to do 2 search and replace action where the order is important.

your file has the comma > , as thousand separator. you want to remove those first.
Select the column that contrains the texted numbers
Put the comma in the search box an keep replace empty then choose replace all
Result>all comma's are removed from the numbers

now while column still selected
Put the point > . into the search box and put the comma in the replace box
and select replace all
Results > now your numbers are converted to real numbers again and problems with summing should be gone.

[Sam Capricci]

I just double clicked on the bottom right of your second sample and it automatically filled down column B and replaced the value errors with numbers.

[roxsingh]

Hi roxsingh,

The trick of multiplying by one is not working well because you also have 1000 separators in the numbers. this makes your conversion trick fail
Another (fast) trick to convert back to numbers is to do 2 search and replace action where the order is important.

your file has the comma > , as thousand separator. you want to remove those first.
Select the column that contrains the texted numbers
Put the comma in the search box an keep replace empty then choose replace all
Result>all comma's are removed from the numbers

now while column still selected
Put the point > . into the search box and put the comma in the replace box
and select replace all
Results > now your numbers are converted to real numbers again and problems with summing should be gone.

Hello,
I got through with the first find and replace and then did teh multiplication by 1.
Thanks soo much.