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]

Cheque No.	Cheque Amount	Occurrence	Dispatch Date
201811000951	2,438.90	recon closed jan-sep-18	26/03/2019
201811000952	962.01	recon closed jan-sep-18	26/03/2019
201811000953	575.34	recon closed jan-sep-18	26/03/2019
201811000504	10,740.92	recon closed jan-sep-18	26/03/2019
201811000505	818.49	recon closed jan-sep-18	26/03/2019
201811000506	4,553.04	recon closed jan-sep-18	26/03/2019
201812002160	3,427.60	recon closed jan-sep-18	26/03/2019
201812002161	2,898.56	recon closed jan-sep-18	26/03/2019
201812002162	3,154.84	recon closed jan-sep-18	26/03/2019
201812002163	4,290.43	recon closed jan-sep-18	26/03/2019
201812002164	573.85	recon closed jan-sep-18	26/03/2019

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[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.