Solved

Excel Cell Format

Posted on 2016-08-17
13
23 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 5

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

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 5

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 31

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 31

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
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.

746 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

12 Experts available now in Live!

Get 1:1 Help Now