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

 

[dUBBINS]

Can you post a sample of how your data is structured, and the actual formula you crafted? Surely someone will sort you out promptly with that posted.

 

[Chrisfav89]

I should add I am trying to Match numbers that are 8 characters long. All start with 000 or 0000.

I've noticed in one of the columns (The one I believe to be the issue) if I place the curser in the beginning of the cell and want to delete the first number, I have to press "delete" twice in order to remove the first 0... Not sure if this adds any clues.

 

[Chrisfav89]

Function is: =match(B2,A:A,0)

Below is a few of the rows. You can see 00006789 and 00006790 exist in both columns.

POSITION_NBR	Position Number
00020206	00006789‬
00033327	00006790‬
00006789	*00006791‬
00029567	*00008262‬
00029568	*00008896‬
00029569	*00009109‬
00029563	*00009936‬
00006790	*00010679‬
00029564	*00010725‬

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Chrisfav89]

So those " * " answer why I have to press delete twice in order to remove the first 0.. Even if I remove those invisible " * " the output is still not correct..

 

[Jonmo1]

I've noticed in one of the columns (The one I believe to be the issue) if I place the curser in the beginning of the cell and want to delete the first number, I have to press "delete" twice in order to remove the first 0... Not sure if this adds any clues.

That does expose an issue.
There is SOMETHING there at the beginning of the cell, before the first 0.

What does this return
=CODE(LEFT(A1,1))

Where A1 is one of those cells (before pressing delete twice)

 

[63falcondude]

It looks like there is some sort of invisible character at the end of the numbers in the second column.

 

[Chrisfav89]

That does expose an issue.
There is SOMETHING there at the beginning of the cell, before the first 0.

What does this return
=CODE(LEFT(A1,1))


Where A1 is one of those cells (before pressing delete twice)

The return is "63"

 

[Jonmo1]

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 ?

 

[SpillerBD]

I've run into that from a xml output from Oracle.
You will end up using a REPLACE or Substitute method to get rid of it.
I haven't run into much since I've started using Power Query more, but it might prove better if you're already on that learning curve.