Invisible "?" in Strings

hellfire45

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

ExpressionValueType
Current_ship_po"?4502575223?"String
Current_ship_line"100.1String

<tbody>
</tbody>


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!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,023
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,025
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Re: Invisible "?" in Strings (help!!! my mind!!!)

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

BGY23

Well-known Member
Joined
Aug 13, 2008
Messages
515
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

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

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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,023
Office Version
365
Platform
Windows
Re: Invisible "?" in Strings (help!!! my mind!!!)

In that case if you want to get rid of it in the sheet use
Code:
Sub chk()
Range("A:A").Replace ChrW(8273), "", xlPart, , , , False, False
End Sub
Change range to suit
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,023
Office Version
365
Platform
Windows
Re: Invisible "?" in Strings (help!!! my mind!!!)

Or in the string
Code:
Current_ship_po = Selection
Current_ship_po = Replace(Current_ship_po, ChrW(8273), "")
 

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
445
Re: Invisible "?" in Strings (help!!! my mind!!!)

In that case if you want to get rid of it in the sheet use
Code:
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):

Code:
  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?".



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(8273), "")
But this doesn't seem to fix it. The variable current_ship_po is still a string equal to "?4505275223?".
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,679
Messages
5,470,097
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top