Invisible "?" in Strings

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
464
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Expression[/TD]
[TD]Value[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]Current_ship_po[/TD]
[TD]"?4502575223?"[/TD]
[TD]String[/TD]
[/TR]
[TR]
[TD]Current_ship_line[/TD]
[TD]"100.1[/TD]
[TD]String[/TD]
[/TR]
</tbody>[/TABLE]


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

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

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

Code:
 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!
 
Re: Invisible "?" in Strings (help!!! my mind!!!)

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

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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...
Code:
[table="width: 500"]
[tr]
	[td]Columns("A").Replace ChrW(8237), "", xlPart, , , , False, False[/td]
[/tr]
[/table]
Your values should all be fine now.
 
Last edited:
Upvote 0
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...
Code:
[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:

Code:
 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.
 
Upvote 0
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))
 
Upvote 0
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

Code:
 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?
 
Upvote 0
Re: Invisible "?" in Strings (help!!! my mind!!!)

Where did the data come from? The web?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top