Can Access remove nonprinting characters from imported data?

[Access_Novice]

I copy some information from a website and then paste/values into Excel. I noticed that some non-printing characters are copied to. Suppose I were to import this Excel spreadsheet into an Access table. Now I need to clean up/remove those non-printing characters. What is the best way to do this in Access?



I suppose I could set up a calculated column with =SUBSTITUTE([InvHours],char(160),"") as the formula, where InvHours is the field name containing the non-printing characters. But if I did this, then I would still retain the original field with the non-printing characters which would increase the size of the database. I don't think I could delete it because then the calculated column wouldn't work. So I would rather not pursue this option if possible.

Would it be possible to somehow run a macro that could automatically be triggered as soon as data is imported, and that macro would clip out the non-printing characters without having to copy anything to blank columns?

Or does Access have some built-in feature that could do this?

[June7]

You could try an UPDATE sql action to modify the imported data. This could be part of the code that runs the import.

REPLACE is the correct function in Access. In Access use Chr instead of Char.

What is the non-printing character? If it's not CR or LF, what is it?

[Access_Novice]

the non printing character is char(160).

[June7]

I know the code is 160, wondered what it represents. I found 160 in ANSI character set table. It is a non-breaking space. I don't know if Access can recognize this character because AFAIK there is no way to create it in Access. If Access can't recognize it then it can't find it to replace it.

The character is showing in imported data as some sort of symbol?

Should be able to exclude the fields from the import.