Two numbers as text not equal...why?

I have a vlookup I must do, and I convert all the codes to text simply because some are numbers and some are letter/number combos. Can someone please explain why in the attached file when I converted these numbers to text from two different sources they don't equal despite being the exact same? Obviously one sees itself as formerly being a number and the other, which came from a CSV file, does not.

I've tried to paste as values, I've checked the lengths with the length function, I'm stumped. Again, I'd just keep them as numbers but unfortunately some of the codes have letter designations with them.

Two Unequal Numbers.xlsx

Thanks!

They have to be text AlKey because as I mentioned sometimes they will have a letter (G or AG) attached to them. Your method doesn't work when I attached a G to the end.

Xor, I tried to paste as values (which probably made them back into numbers huh?) I also tried changing the format at the top to text. What do you suggest? I have to save as text or a "string" because of the mix of letters and numbers, but this was so straightforward it confused me.

Xor, I tried that (turned the format of two cells to text than pasted as values), and it did not work. You can try in the file I sent and you will see.

Ok AlKey that was helpful in getting them to match. Now how does this translate to a Vlookup? How do I get the Vlookup to see them as "Exact".

Thanks.

Index/Match causes the same issue - I added an example where it worked fine from the same sheet: How can I force it (explicitly) to text as the number correctly seen as text is stating. Probably just a silly issue. Once these match exactly, I can do a vlookup, index/match, etc.

Two Unequal Numbersv2.xlsx

Thanks Sktneer, but as I mentioned when XOR did the same thing above, I cannot convert it to a number like you did because sometimes it will have a letter in the name. I want to use text because it could be a number, it could be letters, or it could be a mix of both.

Let's say I have 1174505 and 1174505G. Can't I just convert these to text and look them up? Why can't 1174505 and 1174505G all be converted (I think called "explicit type" in programming languages) to text and then a look made on them? If this was the name Sktneer, I could look that up, why can't I fool excel into thinking 1174505 is a "string as well"?

Originally Posted by sktneer

Please find the attached sheet to see if this is what you are trying to achieve.

Originally Posted by Vaslo

Thanks Sktneer, but as I mentioned when XOR did the same thing above, I cannot convert it to a number like you did because sometimes it will have a letter in the name. I want to use text because it could be a number, it could be letters, or it could be a mix of both.

Let's say I have 1174505 and 1174505G. Can't I just convert these to text and look them up? Why can't 1174505 and 1174505G all be converted (I think called "explicit type" in programming languages) to text and then a look made on them? If this was the name Sktneer, I could look that up, why can't I fool excel into thinking 1174505 is a "string as well"?

Sktneer, I didn't see case 2 - that may work as a solution if nothing more native in the application comes along. That's a sharp idea to append with the "" to force the text. I just don't understand why copying and pasting it to a text formatted box like XOR suggested doesn't work. I'm missing something silly here, probably easy fix. Thanks.