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?
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?