Completed
1. Introduction.
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Fixing N/A Errors in XLOOKUP and VLOOKUP - Hidden Characters Problem
Automatically move to the next video in the Classroom when playback concludes
- 1 1. Introduction.
- 2 2. Using XLOOKUP and getting an #N/A Error.
- 3 3. What cause #N/A? Hidden characters that are unknown.
- 4 4. Check for hidden characters at end of lookup value with RIGHT & CODE function to check.
- 5 5. Lookup Ascii codes to figure out what it is? LOOKUP function.
- 6 6. TRIM to remove Ascii character 32 – a space.
- 7 7. SUBSTITUTE and CHAR9 function to remove Ascii character 9 – a horizontal tab.
- 8 8. SUBSTITUTE and CHAR10 function to remove Ascii character 10 – a a line feed Alt + Enter in Excel.
- 9 9. SUBSTITUTE and CHAR160 function to remove Ascii character 160 – a no-break space.
- 10 10. Determining what characters are causing error, when characters could be anywhere in text, not just at end.
- 11 11. Extract all characters from text string with LEN, SEQUENCE, and MID.
- 12 12. CODE functions to determine the ascii code for hidden characters.
- 13 13. Fix lookup value with many hidden white space characters with REGEXREPLACE.
- 14 14. Summary
- 15 15. Closing
- 16 16. Outtake with formula to substitute multiple characters at one time: CHAR, ARRAYTOTEXT, REDUCE, LAMBDA and SUBSITUTE
- 17 17. Outtake Summary
- 18 18. Outtake Closing
