Replacing Null Characters (ASCII Value "0")

[newyorkpaulie]

I'm trying to replace Null Characters in some data I have imported. The null characters show up as a small black rectangle in the cell.

I tried "edit / replace" but could not figures out how to get a "null character" in the "find what" box. It doesn't seem that you can copy/paste it in.

Also tried the following VB Code.

That does not work either.

Anyone know how to do this?


Sub ReplaceNull()
Null_Character = Chr(0)
Selection.Replace What:=Null_Character, Replacement:="xxxzzz", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

 

[Aladin Akyurek]

Just curious: What result do you get if you apply

=CONCATENATE(A1,"") ?

 

[Yogi Anand]

Hi newyorkpaulie:

A suggestion -- have you tried the CLEAN function together with the TRIM function as in

=CLEAN(A1)

 

[newyorkpaulie]

Now I feel a little dumb !!!

The CLEAN function works great.

I got so focused on the "edit / replace", I didn't even think of CLEAN.

BUT, it would still be optimal to be able to put the NULL character in "replace" to clean up the data in one fell swoop.

Thanks for your help.

 

[Yogi Anand]

Hi newyorkpaulie:

Good Job -- time to celebrate -- not to feel bad. Keep EXCELing!