[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 32
  • Last Modified:

Excel Cell Format

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
jskfan
Asked:
jskfan
  • 3
  • 3
  • 3
  • +1
2 Solutions
 
efrimpolCommented:
You need to select "Paste Special", then ""Text".

You sum function will then work.

Otherwise, it is being copied as "HTML" format.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
jskfanAuthor Commented:
efrimpol

No Text option when you select Paste Special
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
jskfanAuthor Commented:
Subodh Tiwari (Neeraj)
If you press Alt key you cannot type anything
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You won't notice it but it's a space character.
0
 
efrimpolCommented:
What version do you have of Excel? This is screen shot from Excel 2013.
Untitled.jpg
0
 
jskfanAuthor Commented:
I see..
but after you click Paste Special you do not get the option to select TEXT
0
 
efrimpolCommented:
See MS Support link to see if you have this issue and its resolution:

https://support.microsoft.com/en-us/kb/2702629
0
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now