Download Excel File: https://excelisfun.net/files/EMT1868.xlsx
Learn how to detect what hidden characters are causing N/A! Errors In XLOOKUP And VLOOKUP And Fix It!
Many functions in this video: CODE, CHAR, RIGHT, TRIM, LOOKUP, XLOOKUP, ARRAYTOTEXT, REDUCE, LAMBDA, SUBSTITUTE, REGEXREPLACE, REGEXTEST, UNICODE, and more!
ExcelLambda from YouTube supplies awesome solution!
Topics:
1. (00:00) Introduction.
2. (00:27) Using XLOOKUP and getting an #N/A Error.
3. (01:12) What cause #N/A? Hidden characters that are unknown.
4. (01:40) Check for hidden characters at end of lookup value with RIGHT & CODE function to check.
5. (02:29) Lookup Ascii codes to figure out what it is? LOOKUP function.
6. (03:42) TRIM to remove Ascii character 32 – a space.
7. (04:14) SUBSTITUTE and CHAR(9) function to remove Ascii character 9 – a horizontal tab.
8. (04:52) SUBSTITUTE and CHAR(10) function to remove Ascii character 10 – a a line feed (Alt + Enter in Excel).
9. (04:52) SUBSTITUTE and CHAR(160) function to remove Ascii character 160 – a no-break space.
10. (05:04) Determining what characters are causing error, when characters could be anywhere in text, not just at end.
11. (05:19) Extract all characters from text string with LEN, SEQUENCE, and MID.
12. (06:36) CODE functions to determine the ascii code for hidden characters.
13. (07:19) Fix lookup value with many hidden white space characters with REGEXREPLACE.
14. (10:31) Summary
15. (11:12) Closing
16. (11:25) Outtake with formula to substitute multiple characters at one time: CHAR, ARRAYTOTEXT, REDUCE, LAMBDA and SUBSITUTE
17. (16:31) Outtake Summary
18. (16:54) Outtake Closing