Solved

Excel Cell Format

Posted on 2016-08-17
13
24 Views
Last Modified: 2016-09-25
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
0
Comment
Question by:jskfan
  • 3
  • 3
  • 3
  • +1
13 Comments
 
LVL 6

Expert Comment

by:efrimpol
ID: 41759415
You need to select "Paste Special", then ""Text".

You sum function will then work.

Otherwise, it is being copied as "HTML" format.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41759463
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
0
 

Author Comment

by:jskfan
ID: 41759492
efrimpol

No Text option when you select Paste Special
0
 

Author Comment

by:jskfan
ID: 41759494
Subodh Tiwari (Neeraj)
If you press Alt key you cannot type anything
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41759500
You won't notice it but it's a space character.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Expert Comment

by:efrimpol
ID: 41759611
What version do you have of Excel? This is screen shot from Excel 2013.
Untitled.jpg
0
 

Author Comment

by:jskfan
ID: 41759645
I see..
but after you click Paste Special you do not get the option to select TEXT
0
 
LVL 6

Expert Comment

by:efrimpol
ID: 41759664
See MS Support link to see if you have this issue and its resolution:

https://support.microsoft.com/en-us/kb/2702629
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41765109
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
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 41765112
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
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41810206
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel 2016 Not Responding Issues 6 22
Update As Well As Add 6 32
Auto populate in Cascade dropdown 3 24
Filling Blank Cells 14 10
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now