How to remove the spaces before and after the numbers

[Cris_Jon]

How do I remove the spaces before the numbers and after in excel. I tried find and replace and it does not work. I also tried trim still no use.

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"] 300.00 [/TD]
[/TR]
[TR]
[TD] 440.00 [/TD]
[/TR]
[TR]
[TD] 1,694.11 [/TD]
[/TR]
[TR]
[TD] 2,909.54 [/TD]
[/TR]
[TR]
[TD] 5,400.00 [/TD]
[/TR]
[TR]
[TD] 5,572.91 [/TD]
[/TR]
[TR]
[TD] 15,000.00 [/TD]
[/TR]
[TR]
[TD] 16,323.00 [/TD]
[/TR]
[TR]
[TD] 22,406.14 [/TD]
[/TR]
[TR]
[TD] 26,000.00 [/TD]
[/TR]
[TR]
[TD] 3,608,953.30 [/TD]
[/TR]
[TR]
[TD] 11,690,758.54 [/TD]
[/TR]
[TR]
[TD] 274,929.00 [/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to the board.
Have you tried CLEAN?

 

[Rick Rothstein]

Hi & welcome to the board.
Have you tried CLEAN?

CLEAN does not remove non-breaking spaces (ASCII code 160) which is what I suspect the OP has surrounding his numbers (especially since TRIM did not work for him). So, I would suggest the OP try this formula and see if it works...

=SUBSTITUTE(A1,CHAR(160),"")

 

[alvisonhunter]

How do I remove the spaces before the numbers and after in excel. I tried find and replace and it does not work. I also tried trim still no use.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] 300.00[/TD]
[/TR]
[TR]
[TD] 440.00[/TD]
[/TR]
[TR]
[TD] 1,694.11[/TD]
[/TR]
[TR]
[TD] 2,909.54[/TD]
[/TR]
[TR]
[TD] 5,400.00[/TD]
[/TR]
[TR]
[TD] 5,572.91[/TD]
[/TR]
[TR]
[TD] 15,000.00[/TD]
[/TR]
[TR]
[TD] 16,323.00[/TD]
[/TR]
[TR]
[TD] 22,406.14[/TD]
[/TR]
[TR]
[TD] 26,000.00[/TD]
[/TR]
[TR]
[TD] 3,608,953.30[/TD]
[/TR]
[TR]
[TD] 11,690,758.54[/TD]
[/TR]
[TR]
[TD] 274,929.00[/TD]
[/TR]
</tbody>[/TABLE]

Hi there,

We could always remove the first and last character of the given string like this:

=LEFT(MID(A1,2,LEN(A1)),LEN(A1)-2)

This formula will return the string without the first character on the left and the first character on the right, which, in all cases, will be the spaces you are mentioning on this post.

 

[Cris_Jon]

CLEAN does not remove non-breaking spaces (ASCII code 160) which is what I suspect the OP has surrounding his numbers (especially since TRIM did not work for him). So, I would suggest the OP try this formula and see if it works...

=SUBSTITUTE(A1,CHAR(160),"")

Thanks For the Tip Rick. It works.

 

[Cris_Jon]

Thanks for the Tip Alvinsonhunter. It works!

 

[alvisonhunter]

Thanks for the Tip Alvinsonhunter. It works!

You are more than welcome, folk!