Invisible "?" in Strings

[hellfire45]

So I'm not even sure what the best way to describe this is but...

I have a string value in Excel. In the excel sheet it reads as "4502575223". If I click at the end of this string and type backspace, the cursor will not move. If I press it again, it deletes the 3 off the end. It takes 2 backspaces because there is an invisible "?" both before AND after the string.

How do I know there are invisible "?"? Well in VBA, in the locals window, i see this:

Expression	Value	Type
Current_ship_po	"?4502575223?"	String
Current_ship_line	"100.1	String

<tbody>
</tbody>

if I do the following code, the result is false:

 If Left(current_ship_po, 1) = "?" Then current_ship_po = Right(current_ship_po, Len(current_ship_po) - 1)

FALSE

 If Left(current_ship_po, 1) = "" Then current_ship_po = Right(current_ship_po, Len(current_ship_po) - 1)

FALSE

 If Left(current_ship_po, 1) = " " Then current_ship_po = Right(current_ship_po, Len(current_ship_po) - 1)

FALSE

I need to get rid of the "?" on both sides of the string but I can't even get the machine to recognize it's there. Anybody seen this before? How do I code through this mess? Thanks guys!

 

[Fluff]

Re: Invisible "?" in Strings (help!!! my mind!!!)

Apologies I can't read!
it should be ChrW(8237) in both sets of code not 8273

 

[Rick Rothstein]

Re: Invisible "?" in Strings (help!!! my mind!!!)

Assuming the values with this "hidden" character are in Column A, copy/paste the following into the VB editor's Immediate Window and, with the text cursor still on that pasted line of code, press the Enter Key...

[table="width: 500"]
[tr]
	[td]Columns("A").Replace ChrW(8237), "", xlPart, , , , False, False[/td]
[/tr]
[/table]

Your values should all be fine now.

 

[hellfire45]

Re: Invisible "?" in Strings (help!!! my mind!!!)

Assuming the values with this "hidden" character are in Column A, copy/paste the following into the VB editor's Immediate Window and, with the text cursor still on that pasted line of code, press the Enter Key...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Columns("A").Replace ChrW(8237), "", xlPart, , , , False, False[/TD]
[/TR]
</tbody>[/TABLE]

Your values should all be fine now.

This is good good, lovin' it. making progress!

The following code yields the following:

 LAST_SHIP_ROW = .Columns(1).Find("*", , , , xlRows, xlPrevious).Row            
            For X_ROW = 2 To LAST_SHIP_ROW
                        current_ship_po = .Cells(X_ROW, ship_po).Value
                        current_ship_po = Replace(current_ship_po, ChrW(8237), "")

"4502575223?"

So it removed the first "?" but not the last one. How can I get the end part removed? Also, what is this chrw(8237) doing? Are we identifying a code for special character "?" and then using replace to remove it? If so, why doesn't it take care of the second "?" as well?


Thanks for the continue assistance. You guys are great.

 

[Fluff]

Re: Invisible "?" in Strings (help!!! my mind!!!)

I suspect it's because it's not the same character. Try in a blank cell
=UNICODE(RIGHT(A2,1))

 

[hellfire45]

Re: Invisible "?" in Strings (help!!! my mind!!!)

I suspect it's because it's not the same character. Try in a blank cell
=UNICODE(RIGHT(A2,1))

8236

 For X_ROW = 2 To LAST_SHIP_ROW                        current_ship_po = .Cells(X_ROW, ship_po).Value
                        current_ship_po = Replace(current_ship_po, ChrW(8237), "")
                        current_ship_po = Replace(current_ship_po, ChrW(8236), "")

This has fixed the issue but **** how ridiculous is this?

 

[RoryA]

Re: Invisible "?" in Strings (help!!! my mind!!!)

Where did the data come from? The web?

 

[hellfire45]

Re: Invisible "?" in Strings (help!!! my mind!!!)

Where did the data come from? The web?

It's being fed via email from a huge manufacturer. Not really the best way to do things, I know it, but this is the world I live in. I have made a request that they fix this issue on their end but i dunno if they'll do it.

 

[hellfire45]

Re: Invisible "?" in Strings (help!!! my mind!!!)

Anyway, thank you friends!