Forum Discussion
Used xlookup and came back with result #n/a although text fields are the same
Thanks for your help. Appreciated.
I have tried to use TRIM function to make the correct, but it was not working.
I think you are right on trailing spaces in the target string in column C. I just tried to use =SUBSTITUTE(C2,CHAR(160)," ") to fix it and it came out some improvements. Below is the picture now (Column U is result of xlookup after replacing the Column C with formula result):
Do you think if you can provide me suggestion for the last two #N/As? Thanks,
TANACAL125 To find which characters are causing the problem, you can perhaps use the formula demonstrated in the attached workbook. It lists out each and every character in your text and puts the corresponding code next to it. That way, you can visualise the white space in your text and use TRIM, CLEAN and/or SUBSTITUTE to get rid of them.
Thanks, Riny. I think this is exactly what I was looking for. By the way, once I paste the text, how do I find out which character to fix? Is there any reference for code? I am sorry I am new to this and need your further help please. Thanks, appreciated.
TANACAL125 Sorry, I wasn't clear. Copy columns B, C and D and paste them somewhere towards the right. Then enter your other text (the one that looks the same but doesn't match). Now compare the two code columns to see what the character codes don't match.
Riny_van_Eekelen Thanks so much for your explanation. Appreciated.
May I ask you for one more help? Can you help me to understand the formulas?
=MID(B2,SEQUENCE(LEN(B2)),1)
=CODE(C2#) [can you help me with # in the formula as well? 🙂 ]
Much appreciated.