field with spaces that need to be removed

[guaya81]

Good day guys and girls

I have an issue and need to find a way to resolve it
there is a column that has an amount and in that field, the number has 6 spaces and cant removed with formula I have to do it manually

$ 1,632.00 I try to replace, =substittute(h2," ",""), =substittute(h2," ","") and they dont work for me

do you have any suggestion


thanks

 

[Rick Rothstein]

Good day guys and girls

I have an issue and need to find a way to resolve it
there is a column that has an amount and in that field, the number has 6 spaces and cant removed with formula I have to do it manually

$ 1,632.00 I try to replace, =substittute(h2," ",""), =substittute(h2," ","") and they dont work for me

If you copied the values from another source (especially if you did so from a web page), then those spaces are probably not regular spaces (ASCII 32), rather, they are probably non-breaking spaces (ASCII 160). Give this formula a try...

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

 

[guaya81]

Mr. Rick

thank you I didn't work but I guess is another issue and I can use your formula in another place better, always learning something

thanks

 

[Fluff]

If the spaces are after the $ sign, put this in a blank cell & what does it return?
=CODE(MID(H2,2,1))

 

[Rick Rothstein]

Mr. Rick

thank you I didn't work but I guess is another issue and I can use your formula in another place better, always learning something

You should answer Fluff's question in Message #4 as what he is thinking about could be behind your problem. Following up on my post though, perhaps you have a mix of normal and non-breaking spaces. With that in mind, give this formula a try...

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