The customer names column in one Excel file has diacritics and the other doesn't, so names don't match between the files; how do I fix it?

Why the names do not match

Lookup functions in spreadsheets, such as VLOOKUP, XLOOKUP and MATCH, compare text character by character, and to a computer a vowel mark is a character. «مُحَمَّد عَلِيّ» contains 7 characters that «محمد علي» does not: 5 harakat and 2 shaddas. To the eye it is one name; to the sheet they are two different texts, and the lookup returns a not-found error.

It usually happens when one file comes from a system that stores names as their owners typed them, like a sign-up form or a booking platform, and the other from quick manual entry with no marks.

A whole column as an example

A column of three cells: «مُحَمَّد عَلِيّ», then «فَاطِمَة الزَّهْرَاء», then «نورة سعد», already written without marks. With the default groups it came back as three lines in the same order:

• محمد علي
• فاطمة الزهراء
• نورة سعد

14 marks were removed: 10 harakat, 3 shaddas and one sukun. The third line did not change, since it had no marks, and it stayed in place. That is what matters in a sheet: the number of lines out equals the number in, so every name returns to its row.

Steps

1. In Excel, select the cells of the vocalized names column and copy them. Each cell becomes a line when pasted.
2. Paste into the Arabic diacritic remover and leave tanwin, harakat, shadda and sukun on.
3. Press «Copy result».
4. Back in Excel, pick the first cell of a new column next to the original and paste. A new column keeps the vocalized original intact.
5. Use the new column in your lookup instead of the original.

What this step does not fix

• Hamza: if one file has «أحمد» and the other «احمد», they still will not match, because the tool does not turn «أ» into «ا». That is spelling normalization, not diacritic removal, and the tool does not do it.
• Ta marbuta and ha: «فاطمة» and «فاطمه» stay different for the same reason.
• Extra spaces: a trailing space in a cell also blocks matching, and the tool does not touch spaces. Use the TRIM function in the sheet or clean the column before pasting.
• Stretched letters: the tool counts them but does not remove them. If a note about them appears under the counters, that is another reason for the mismatch.

If your goal is a unique customer list rather than matching two files, run the stripped list through the Duplicate-line remover. That tool can also ignore diacritics when matching without removing them from the line it keeps, if you want to hold on to the vocalized spelling.