Most of the punctuation in the top row of your keyboard is in the 33 to 47 range, and a space is character 32.Ĭells H14 and H18 show that column D is using character 160 for spaces instead of character 32. While you can search the web for a table of ASCII codes, it helps to remember a few basics: Capital letters A through Z run from code 65 to 90. In order to check if the spaces are causing the problem, set up two columns that reveal the ASCII character code for each character in cells A2 and D3.
Because web browsers will ignore repeated spaces, many websites will generate something called a nonbreaking space instead of a regular space. The data in column D was downloaded from the web. It turns out that the company in Nashville had recently upgraded from an old employee tracking system to a new, cloud-based system. In this instance, however, the data didn’t have leading or trailing spaces. Copy the cells containing the TRIM formula and use Paste Values to replace the original data with the corrected data. For example, =TRIM(“ FIRST LAST ”) would return FIRST LAST.Įnter the TRIM formula in a blank column adjacent to your data. TRIM will remove any leading spaces or trailing spaces and will replace repeated spaces with a single space.
If you do encounter spaces at the end of one column and not the other column, use a formula such as =TRIM(A2) to remove extra spaces. You also could use a formula such as =LEN(A2) and =LEN(D3) to learn how many characters are in each cell. Then check if the flashing insertion cursor is next to the last letter or if it’s a space or more to the right of the last letter. The quick way to test is to select cell A2 and press the F2 key to put the cell in Edit mode. Whenever I encounter a VLOOKUP or MATCH function failing, my first impulse is that one cell has trailing spaces.
Why is the MATCH function not seeing them as a match?
Similar problems happen in cells C6 and C7.Įverything in cells A2 and D3 looks exactly the same to the human eye. But for some reason, the formula isn’t detecting that Billy Ray Smith in cell D3 can be found in cell A2. The formula also appears to work in C4, which finds that the name in cell D4 appears in cell A3. Adam Alton appears in cell D2 but isn’t anywhere in column A. The MATCH formula in column C appears to work in cell C2. The old list is in column A, and the new list is in column D.
0 kommentar(er)
