Completed
10. Determining what characters are causing error, when characters could be anywhere in text, not just at end.
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
