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!!!)

in a blank cell enter
=CODE(A2)
where A2 is the cell containing your string, what does it return

 

[RoryA]

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

Or in code use AscW(current_ship_po) to determine the character number.

 

[BGY23]

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

Hi,
I don't know about Vba but using the trim funcion =trim(string) usually removes any surplus spaces, including leading or trailing, in a string. Maybe you can incorporate in the fomula in your code.

 

[hellfire45]

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

in a blank cell enter
=CODE(A2)
where A2 is the cell containing your string, what does it return

This code function returns: 63

 

[hellfire45]

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

Hi,
I don't know about Vba but using the trim funcion =trim(string) usually removes any surplus spaces, including leading or trailing, in a string. Maybe you can incorporate in the fomula in your code.

Trim doesn't work sadly.

 

[hellfire45]

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

Or in code use AscW(current_ship_po) to determine the character number.

So after I set the current_ship_po variable to that string with the "?" I ran it through AscW(current_ship_po).

The result was "8237".

 

[Fluff]

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

In that case if you want to get rid of it in the sheet use

Sub chk()
Range("A:A").Replace ChrW(8273), "", xlPart, , , , False, False
End Sub

Change range to suit

 

[Fluff]

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

Or in the string

Current_ship_po = Selection
Current_ship_po = Replace(Current_ship_po, ChrW(8273), "")

 

[hellfire45]

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

In that case if you want to get rid of it in the sheet use

Sub chk()
Range("A:A").Replace ChrW(8273), "", xlPart, , , , False, False
End Sub

Change range to suit

So I did the below as you suggested (I think):

  LAST_SHIP_ROW = .Columns(1).Find("*", , , , xlRows, xlPrevious).Row            
            For X_ROW = 2 To LAST_SHIP_ROW
                        .Range("O:O").Replace ChrW(8273), "", xlPart, , , , False, False
                        current_ship_po = .Cells(X_ROW, ship_po).Value

But this doesn't seem to fix it. The variable current_ship_po is still a string equal to "?4505275223?".

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(8273), "")

But this doesn't seem to fix it. The variable current_ship_po is still a string equal to "?4505275223?".