Problem with an easy formula VLOOKUP doesnt match

[XLalbania]

Hello Excel friends
I'm tring to fix a bug with excel
This is just small sample and VLOOKUP seems not to find a Value
and the problem is only with the string (ir) beacuse look differet from the other (ir)

	A	B	C	D	E	F	G	H	I	J
1	Table			Dropdown	Result				for data validation
2	ir	25%		ir	#N/D				ir
3	ab	33%							ab
4	ff	44%							ff
5	gg	62%							gg

=VLOOKUP(D2;A2:B5;2;0)

Thank You for your help

[AliGW]

It isn't an Excel bug. There is an invisible space after the ir in cell A2 - get rid of that and your formula works.

[XLalbania]

Thank you for your help
How can I get rid of that nvisable space? because in not a space othewise i Would use TRIM function, As i said this is just small sample but I've 100000 of records and (Ir) is not the only problem, i think I need a formula to avoid thi invisable NOT charcer

Thank you so much

[AliGW]

How is the ir getting there in the first place?

Select the column and use find and replace:

Find: ir*
Replace: ir

[XLalbania]

If you edit the string and you press backspase you wil see that there is no blank space, but if you press again Backspace you will notice that does't cancel (r) because there is a kind of charter that make different string

I Have some ir differnt from other ir some of them work and other not, probably because i download a file from my database from work but i cannot fix 10000 of (ir) so I was wondering if there is a formula
Thank You

[XLalbania]

Thanks everybody for your help, for the moment I wil stay with AliGW suggestion, but we keep this UNSOLVED in case there are other suggestion
Thank you for your help

[AliGW]

A formula is not the way forward: cleaning your source data is the way to deal with this. You will need to find out what the rogue character is and why it is getting there.

[philaugust2004]

=REPLACE(A2,1,3,"ir")

That will remove the invisible character

[TMS]

=LEN(A2)

gives 3

=CODE(RIGHT(A2,1))

gives 63

=VLOOKUP(D2&"*",A2:B5,2,0)

returns 25%

In this instance, it is only cell A2 that has an extra, "invisible", character.

[XLalbania]

Wooow!! mr.TMS
Excellent Job!! Is what i needed Now the Topic is SOLVED
Thank you everybody else tha helped me
Thank You!

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[XLalbania]

Ok I did Every thing as you said
Goodbye