[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel Cell Format

Posted on 2016-08-17
13
Medium Priority
?
31 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 33

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41759500
You won't notice it but it's a space character.
0
 
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 33

Accepted Solution

by:
Rob Henson earned 2000 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 33

Assisted Solution

by:Rob Henson
Rob Henson earned 2000 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 33

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

656 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