Need Excel Formula to trim spaces before Numbers

[raajnabriz]

[FONT=&quot]Hi there,[/FONT]
[FONT=&quot]Please provide excel formula to trim spaces before numbers, i have received large data that contains spaces in each cell before the amount. I am using MS Office 2010.[/FONT]
[FONT=&quot]Thanks,[/FONT]
[FONT=&quot]Raaj.[/FONT]

 

[Special-K99]

Example data?

 

[jasonb75]

Have you tried using the TRIM() function?

 

[raajnabriz]

Yes, its not working, the problem at present is when i select two or more cells to check the total, it shows count only.

 

[raajnabriz]

[TABLE="width: 544"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Cheque No.[/TD]
[TD]Cheque Amount [/TD]
[TD] Occurrence [/TD]
[TD]Dispatch Date[/TD]
[/TR]
[TR]
[TD]201811000951 [/TD]
[TD] 2,438.90 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201811000952 [/TD]
[TD] 962.01 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201811000953 [/TD]
[TD] 575.34 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201811000504 [/TD]
[TD] 10,740.92 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201811000505 [/TD]
[TD] 818.49 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201811000506 [/TD]
[TD] 4,553.04 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201812002160 [/TD]
[TD] 3,427.60 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201812002161 [/TD]
[TD] 2,898.56 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201812002162 [/TD]
[TD] 3,154.84 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201812002163 [/TD]
[TD] 4,290.43 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
[TR]
[TD]201812002164 [/TD]
[TD] 573.85 [/TD]
[TD="align: left"]recon closed jan-sep-18[/TD]
[TD]26/03/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

If you copied the data from the Web, then maybe
=TRIM(SUBSTITUTE(A1,CHAR(160),""))

 

[Fluff]

From the data you've posted it doesn't look like you have problems with spaces.
In a blank cell put
=B2*1
What is returned?

 

[raajnabriz]

It return #value !

 

[raajnabriz]

This formula works, but i cannot get total at the bottom only count, even though the sum option is selected.

 

[raajnabriz]

I have received the data through mail from our clients.