remove spaces in cells

**lbullen** · 09-11-2009, 06:07 AM

Hi

I have some amounts in excel that have a space in them where I need the space removed
Ie 40 250.00 and I need them to show as 40250.00
Or 1 250.53 which needs to be 1250.53

The find and replace does not work, neither does the substitute function, because of the decimal point. IF statements do not seem to work either…

I cannot remove the decimals by decreasing the number of decimals as it does not recognise the value as an amount because of the space!

Has anyone got any bright ideas on this? Many Thanks

Lorna

**DonkeyOte** · 09-11-2009, 06:10 AM

You say find and replace of space with nothing does not work ?

It should do - you just need to ensure format of cells is subsequently set to number and 2 decimal places (to get 40250.00 rather than 40250)

If it doesn't - you need to establish which CHAR code is in play... it's obviously not space (32) ... so using the 40 250.00 example and assuming this is stoted in A1 in B1 enter: =CODE(MID(A1,3,1)) ... what is the output ?

**lbullen** · 09-11-2009, 07:09 AM

Thanks

The output is 32...

**DonkeyOte** · 09-11-2009, 07:13 AM

So it is a space... can you post a sample ?

If you run an Edit -> Replace and replace space with nothing what happens - presumably 40 250.00 become 40250 ?

**lbullen** · 09-11-2009, 07:16 AM

Hi Again

OK, find and replace is now working! Instead of putting a space in the find bar with the space bar, I copied the actual space in my number and pasted it into the find bar....

Thanks anyway for your help