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 he
"
"

Thanks for any help.

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


---
HTH

Bob Phillips


"Mike H." wrote in message
...
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 he
"
"

Thanks for any help.

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


"Bob Phillips" wrote:

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


---
HTH

Bob Phillips


"Mike H." wrote in message
...
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 he
"
"

Thanks for any help.



.

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.





Mike H. wrote:

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 he
"
"

Thanks for any help.

--

Dave Peterson

Not a range Mike, just a single character.

---
HTH

Bob Phillips

"Mike H." wrote in message
...
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


"Bob Phillips" wrote:

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


---
HTH

Bob Phillips


"Mike H." wrote in message
...
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 he
"
"

Thanks for any help.



.

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!

=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 he
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.)


Mike H. wrote:

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

Just a typo or two:

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

Dave Peterson wrote:

=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 he
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.)

Mike H. wrote:

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

--

Dave Peterson