Match function refuses to work

[Chrisfav89]

Hello,

I have been trying to pull some data via Index(match. The match function is not working and I'm not sure why. I can see there should be a match and cannot figure out why.

I've fiddled with the cells format each individually, then by Text to Columns.. No luck.

I asked excel if the two cells = each other and the output was "false" as expected. Then I asked excel if each cell was text (ISTEXT) and the output was "true" for each cell.

So if excel is telling me they are both text, and I can see that the numbers match identically. I've confirmed there are no random spaces in each cell.

Now I'm puzzled as to why the match function won't work.. Any ideas? Has anyone ran into this issue before?

Thanks

 

[Chrisfav89]

63, really?
That's a question mark ? character.

Did you change A1 to a cell you described having to press delete twice to remove the first 0 ?

No, it was column B that had this issue.

Column A returns "48" with that formula. And If I delete the invisible character in column B the formula returns "48" as well. However the match still does not return a value.

 

[Jonmo1]

ok, I'm asking to do that formula on a cell you know has the problem BEFORE making any changes to that cell.

 

[Chrisfav89]

Right. It is 63 before I make any changes to the problem cells.

Not sure if this helps but I have a feeling the way these were originally entered were by putting a " ' " before the number so the zeros would stick. So " '00006970 "

I've seen this done on other files.

 

[Jonmo1]

That's really wierd because 63 is the question mark character.
You don't see that in the cell ?

Try using Find/Replace to replace ? with nothing

Find replace may end up treating the ? as a wild card, and replace anything..
Might need to enter it as ~?

 

[63falcondude]

That's really wierd because 63 is the question mark character.
You don't see that in the cell ?

Try using Find/Replace to replace ? with nothing

Find replace may end up treating the ? as a wild card, and replace anything..
Might need to enter it as ~?

I copied and pasted his sample data and found that =CODE(RIGHT(B2,1)) returns 63. If you go to the far right of the string and hit backspace, it deletes an invisible character. After some research, it looks like Excel also gives 63 for characters above the ASCII code 255.

 

[philaugust2004]

Copying your data into excel I get a "?" at the end of each cell in col B

 

[FormR]

The return is "63"

If the char is not actually a "?" then this usually means that the CODE() function does recognise the character.

If you have Excel 2013 or higher you can try the following to identify the invisible character (after changing the cell ref etc):

=UNICODE(LEFT(A1,1))