Finding unprintable characters in data

  • Thread starter Thread starter Nich
  • Start date Start date
N

Nich

I imported data which contained cr/lf or char(13). How do I find these
characters and strip them out?
 
You look for CHR(13) and CHR(10) with instr() function to
find and left, right and mid functions to remove.

Regards

Kevin
 
First do a backup of the database just in case things go wrong.

UPDATE YourTable
SET YourTable.YourField
= Replace([YourTable]![YourField],Chr(13)," ");

The above will put a space in place of the Chr(13).

Also Chr(10) and Chr(13) often work together. You may have to find both.
 
I imported data which contained cr/lf or char(13). How do I find these
characters and strip them out?
Click to expand...

That's curious: I just answered an almost identical question from "Dave".
Here's what I told him, which you should be able to adapt:

You'll need to find out what it is. My guess is that it's a linefeed Chr(10) -
it wouldn't hurt to make a backup of your database just in case, and run a
query

SELECT memofield, InStr([memofield], Chr(10))
FROM tablename
WHERE memofield LIKE "*" & Chr(10) & "*"

to see if it shows the records with the box and the position of the linefeed.
Count characters and see if it matches.

You could then run an Update query updating the memo field:

UPDATE tablename
SET memofield = Replace([memofield], Chr(10), "")

Note that if the field has legitimate carriage return-linefeed characters this
will do more harm than good!
 
Back
Top