Excel 2007 : removing spaces

**bjamin_w** · 02-19-2010, 08:27 AM

i have imported some data from an accounting programme, but the numbers have come in as a text string

eg "345 456,54"

i want to remove the spaces and convert so that excel understands it as a number 345456.54

any ideas?

**jeffreybrown** · 02-19-2010, 08:31 AM

To remove the spaces try =SUBSTITUTE(A1," ","")

**bjamin_w** · 02-19-2010, 08:50 AM

tried this, didnt work. Any other ideas?

**DonkeyOte** · 02-19-2010, 08:52 AM

Try CHAR(160)

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

If that does not work you need to identify the offending CHAR - using your sample you can do this using: =CODE(MID(A1,4,1))

EDIT: hang on... given you're in the UK:

=--SUBSTITUTE(SUBSTITUTE(A1," ",""),",",".")