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]

 

[jasonb75]

When you trim, the numbers will still be in text format, try =TRIM()+0 to convert them back to numbers, then they should sum correctly.

Alternatively, select all of the cells with the amounts, then use find and replace (ctrl h), enter a single space in the Find box, leave the Replace box empty, then Repalce All.

 

[raajnabriz]

Thank you all for your gr8 effort in helping me out, I just received the required formula from a wonderful youtuber named Ms. Leila Gharani [thank you Ms. Leila].

 

[jasonb75]

You also received the required answer here 3 days ago.

If the answer here was wrong then so was the question.

 

[raajnabriz]

You also received the required answer here 3 days ago.

If the answer here was wrong then so was the question.

Sorry Jason, I didn't mean to demean anyone of you there, but the correct formula that helped me was this

=VALUE(TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))) This not only trimmed the spaces but also helped in getting the sum total of selected cells on the status bar and that was what I had requested. Sorry once again if may be my question was not thoroughly explained by me. I have received many a good work from this forum since many years and I thank everyone for that. Raaj.

<tbody>
</tbody>