Illegal Character Imported

[Mike H.]

I am importing data from another system into an Excel File. In some cells I
get a character that in Excel looks like a question mark with a border around
it. If I copy that character and paste it in the VBA editor, it appears as
two Double Quotation marks "" with space between them and they are stacked
vertically. Anyway, my question is, how can I test of the existence of this
character in the cell. If I can detect it is in there I can create a
work-around for my data. Is there some kind of a VBA command that checks for
the existence of specific ascii characters (I don't know what ascii character
it is.) Below is a copy of it, pasted in here:
"
"

Thanks for any help.

 

[Bob Phillips]

Maybe it is unicode. Have you tried capturing it with ASCW(character)?

 

[Mike H.]

I tried this but get an error. How do you mean to try capturing it?

Sub test()
Dim X As Variant
Let X = AscW(Cells(43, 1345).Value)
End Sub

 

[Dave Peterson]

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Then using instr would seem like a reasonable approach.

If you still have trouble, use Chip's routine to find the character. Post back
with what you found. (I can't see what it is in your message.)

It'll make it easier for others to test and help.

 

[Bob Phillips]

Not a range Mike, just a single character.

 

[Mike H.]

I ran the View Cell on just the character in question that is in a cell.
Here are the results:
Pos 001
Char (it shows a box)
Dec 013
Special ^

If I toggle to Hex, it changes the Dec 013 to Hex xD

Variable Type is String

When I leave the character in a cell that had it in it, it then occupies 2
positions. The information is:

Dec 013 for the first one
Dec 010 for the second one
Both have ^ as special characters.

Hex is xD & then xA

Thanks for any help!

 

[Dave Peterson]

=char(10) or hex-A or vbLf or chr(10) is the same as alt-enter (a linefeed).

=char(13) or hex-D or vbCr or chr(13) is the carriage return character.

So those two characters team up to form a carriage character/linefeed
combination. VBA has a constant for that, too: vbCrLf

I'd use some code like:

with worksheets("sheet9999")
.cells.replace what:=vbcrlf, _
Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
End with

Use
Replacement:=vblf, _

and format the cell/range to wraptext if you want to keep the alt-enter
functionality.

(You could also replace the vbcr character with "" to accomplish the same
thing.)

=========
This is my usual post for fixing these kinds of invalid characters--I didn't
post it because I didn't know what you really wanted.

Saved from a previous post.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##)) '<--What showed up in CellView?

myGoodChars = Array("")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Dave Peterson]

Just a typo or two:

So those two characters team up to form a carriage return/linefeed character
combination...