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! I answer the question: Can you use SUBSTITUTE for many values without nesting? The answer is yes!
Learn how to get the SUBSTITUTE function to replace more than one value! Other ways to phrase this issue:
Multiple Replacements with Excel
Substitute multiple values
SUBSTITUTE function with dynamic array
Excel substitute function array of substitute values
Excel: find and replace multiple values at once
Excel substitute multiple sub text strings with one formula
Excel – Can you use SUBSTITUTE for many values without nesting?
Many functions in this video: CODE, CHAR, RIGHT, TRIM, LOOKUP, XLOOKUP, ARRAYTOTEXT, REDUCE, LAMBDA and SUBSTITUTE.
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 characters.
14. (07:22) Formula to substitute multiple characters at one time: CHAR, ARRAYTOTEXT, REDUCE, LAMBDA and SUBSITUTE
15. (12:22) Summary
16. (12:45) Closing