Excel Cell Format

jskfan
jskfan used Ask the Experts™
on
Excel Cell Format


I have copied from a webpage a bunch of numbers to Excel spreadsheet. Then I selected all the cells that contain the numbers and made sure the Cell Format is a number with 2 Decimals. However when I do the sum of the numbers I get 0.00 instead of the real sum


Any help on this issue ?

Thanks


22,359,366.75 
7,089,179 
6,263,388.75 
2,870,487.12 
2,101,038.31 
2,016,783.63 
1,601,283.24 
1,335,005.42 
1,302,646.06 

Total 0.00
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You need to select "Paste Special", then ""Text".

You sum function will then work.

Otherwise, it is being copied as "HTML" format.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
See if that resolves your issue.

Select those numbers --> Press Ctrl + H --> Click In the Find what box --> Press Alt key and type 0160 --> Click on Replace All --> OK

Author

Commented:
efrimpol

No Text option when you select Paste Special

Author

Commented:
Subodh Tiwari (Neeraj)
If you press Alt key you cannot type anything
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You won't notice it but it's a space character.
What version do you have of Excel? This is screen shot from Excel 2013.
Untitled.jpg

Author

Commented:
I see..
but after you click Paste Special you do not get the option to select TEXT
See MS Support link to see if you have this issue and its resolution:

https://support.microsoft.com/en-us/kb/2702629
Finance Analyst
Commented:
The contents of the clipboard are getting pasted as TEXT anyway (Plain text or HTML makes no difference) which is why the SUM is not working.

Against each cell there is probably a little Green Triangle in the top left and when selecting one of the cells a little dropdown should appear to the left. One of the options in this dropdown will be Convert to Number.

If that is the case, select the whole set of numbers and use the Convert to Number option in the first selected cell. All will then be converted and the SUM should work.

Thanks
Rob H
Rob HensonFinance Analyst
Commented:
Alternatively, if you don't want that you can allow for the cells being text by using an Array formula to sum them.

Where you have a typical formula:

=SUM(B2:B10)

Change to:

=SUM(B2:B10*1) and rather than just pressing Enter, press Shift, Ctrl & Enter. In the formula bar the formula will then show as:

{=SUM(B2:B10*1)}

Typing the { } manually will not the same effect.

Thanks
Rob H
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
The solution posted by me in Post ID: https:#a41759463 and Post ID: https:#a41759500 also take care of the issue and they efficiently resolved the question first.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial