Convert to amounts

[Shabbo]

Dear Sir,

I am not able to calculate total of the numbers in attached sheet can you please advise the best solution.

When I select one more cell its giving only count but I want sum as well.

 

[vletm]

Shabbo
Get Your data as numbers not as text
then no need to do extra work!

 

[Shabbo]

Shabbo
Get Your data as numbers not as text
then no need to do extra work!

but Its already text now, how to convert into numbers now.

 

[vletm]

Yes ... it's text
why to get 'number'-data as texts
if You want to use it as numbers?
You asked to "advise the best solution."

 

[vletm]

Shabbo >> if cannot get as numbers >> use next link
https://www.thespreadsheetguru.com/the-code-vault/2014/8/21/convert-numbers-stored-as-text

 

[p45cal]

A quick examination of your workbook shows that many of the cells contain the character ascii code 160 (a non-breaking space, did you paste this data from the web? If you look at the source of the web page it would likely appear as &nbsp) So I suggest one of two things:
1. Run a one line macro which will work on the entire active sheet:

Sub blah()
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
End Sub

or
2. follow the following instructions to the letter:

• Select any cell with what looks like a number but you've found isn't a number. I've used H12 here:

and I've selected the whole value in the formula bar to show there are leading characters.

• Select only one of those leading characters:

• (they're quite narrow, use the arrow keys on the keyboard in conjunction with the shift key to be sure you only select one of them)
  
• then on the keyboard put that single character into the clipboard with Ctrl+c on the keyboard.
• Press the escape key.
• Make sure you only have one (any) cell selected on the sheet (this means the whole sheet will be processed) or select a single range of cells that you want to limit the processing to.
  
• Press Ctrl+h (this'll bring up the search and replace dialogue box).
• In the Find what: field make sure it's empty first, then press Ctrl+v to paste that single character in there (you'll see nothing obvious in that field).
• Then make sure the Replace with: field is empty.
• The rest of the dialogue box should look like this:

• Click on Replace All.
• Dismiss the report message
• Click Close.

That's it, you've finished.

The attached contains the macro. Click the button on the sheet to run it.