Forum Discussion
Used xlookup and came back with result #n/a although text fields are the same
tanacalIs it possible you have one or more trailing spaces in the target string in column C or the lookup value in Column Q? Try using TRIM function to correct for this.
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.
