Remove strange non-printing character

[Jubjab]

Hi,

I am importing some data from a very non-excel-supportive database. The only export option in the database is RTF. It uses text boxes for all data. I open it in word, "save as" as text file (.txt), and open in Excel. All monetary values over 1000 are imported with a space (i.e. 50000 is imported as 50 000).

The problem is that even though it appears to be a normal space character, I can't seem to remove it automatically.

* Edit->Replace with a space in the search box does not work
* According to the CODE function, the space is chr(160). A VBA replace with that does, however, not work.
* TRIM will not get rid of the space

If I ctrl-C the blank space and paste it into the replace box, it works, but only directly after I have opened the file. After I have run my sorting macro, it won't work anymore. I tried recording a macro when running this ctrl-C operation, and it recorded a normal blank space (which doesn't work if run).

What makes it even more strange, is that the file I open is in txt format!

Note that I can't make the replace before anything else (while it still works), as I don't want to remove the spaces from text fields.

Any ideas?

 

[al_b_cnu]

Hi,

Would this do it: =SUBSTITUTE(A1,CHAR(160),".")

 

[pgc01]

Hi Jubjab

According to the CODE function, the space is chr(160). A VBA replace with that does, however, not work.

I've just tried it and it worked. Replaced it with a normal space

Range("A1").Replace Chr(160), " ", lookat:=xlPart

Hope this helps
PGC

 

[Jubjab]

Hi,

Would this do it: =SUBSTITUTE(A1,CHAR(160),".")

It appears this is working. I will still have to try it tomorrow with my live file.