Normalize Arabic text for search and matching

Why searching احمد does not find أحمد

Search and comparison work character by character. Alef with hamza (أ) and bare alef (ا) are two different Unicode characters, so to a computer أحمد and احمد are simply different strings, however close they look to a reader.

The same thing happens with several pairs that Arabic writers use interchangeably, especially when typing quickly on a phone:

The W3C draft note on string searching points out that Arabic, Persian and Urdu users generally do not type short vowels, and recommends that a search term without diacritics should match text that has them. Plenty of spreadsheets, apps and databases do not do this on their own, though. The practical answer is to normalize the text yourself, in a copy made for searching.

What each Cleaner option changes

In the Cleaner, open More controls and find the group Normalize letters for search. It has four options:

• أ إ آ → ا (including decomposed forms) turns alef with hamza above or below and alef with madda into bare alef, including when the hamza or madda was typed as a separate mark after the alef.
• ى → ي turns alef maqsura into yaa.
• ة → ه turns taa marbuta into haa.
• Persian ی ک → Arabic ي ك replaces the Persian yeh and kaf with their Arabic counterparts.

أحمد إبراهيم
مدرسة الأمل
مستشفى الشفاء

احمد ابراهيم
مدرسه الامل
مستشفي الشفاء

Each option works in one direction only. The tool never turns ه into ة or ي into ى, because that would require knowing the word. What matters is that both spellings end up in the same form.

For vowelled text, add two options from Arabic marks: Remove common Arabic diacritics (short vowels, tanween, shadda, sukun and superscript alef) and Remove tatweel.

إِنَّ الْعِلْمَ نُــورٌ

ان العلم نور

Some things are left alone on purpose: hamza on waw (ؤ) or on yaa (ئ), standalone hamza (ء), alef wasla (ٱ) in Quranic text, and spacing inside names such as عبد الله versus عبدالله. Those need a decision from you or a targeted find and replace.

The golden rule: normalize a copy, never the text you publish

The Cleaner prints a warning right above these options: These options change spelling. Do not use them for quotations or text that must stay verbatim. Normalization erases real distinctions. على is a preposition and علي is a name; المكتبة (the library) becomes المكتبه, which is simply misspelled:

وضع علي الكتاب على طاولة المكتبة

وضع علي الكتاب علي طاوله المكتبه

That output is a perfectly good search key and a spelling error anywhere a person will read it. So always keep two versions:

1. The original, which you display, print and send.
2. The search key, a normalized copy used only for matching, in its own column or file.

Apply the same rules to both sides of every comparison. If you normalize a column of names, normalize the search term too; otherwise you will be searching for أحمد in a column that no longer contains a single hamza. And use the same set of options every time: a key built without ة → ه will not match a key built with it.

Persian yeh and kaf: letters that look Arabic but do not match

Text that looks entirely Arabic can contain the Persian letters ی and ک, especially if it was copied from a Persian website or typed on a Persian keyboard. According to Unicode, Farsi yeh has two dots only in its initial and medial forms and none at the end of a word. That is why علی looks exactly like على, even though it is a third character that matches neither على nor علي. Keheh (ک) is likewise its own character, used for Persian, Urdu and Sindhi.

Tick Persian ی ک → Arabic ي ك together with ى → ي and all three shapes collapse into one:

مستشفی
کتاب
على

مستشفي
كتاب
علي

To decide when to keep Persian letters and when to replace them in the text itself, read the guide to Persian and Arabic letters.

Matching two lists of names

A typical case: a registration list exported from an online form and an attendance list typed by someone else. Same people, different spelling. Compare them directly in Lists with Intersection and you only catch names spelled character for character the same:

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

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

منى سالم

A more reliable method:

1. Paste the first list into the Cleaner, tick all four Normalize letters for search options plus Remove common Arabic diacritics, and copy the result.
2. Do the same with the second list without changing any option.
3. Open Lists and paste the two normalized copies into List A and List B.
4. Choose Intersection to see shared names, or A minus B to see who is only on the first list.

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

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

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

The result shows the normalized spelling, not the original. To get back to the name as its owner wrote it, keep the original and the key side by side in a spreadsheet and look the key up there. Tick Ignore edge spaces for matching if some lines end with a stray space.

To find duplicates inside a single list, combine the normalization options in the Cleaner with Remove exact duplicate lines. Duplicate removal runs after normalization, so فاطمه is dropped when فاطمة came first.

Comparing two versions while ignoring marks and alef forms

When you review an edited version of a text, the changes that matter can drown in dozens of differences in vowel marks and hamza. Compare has a panel called Comparison options only — originals preserved with Ignore common diacritics, Ignore tatweel, Normalize alef variants and Normalize ordinary spaces. These apply only to the copies being compared; both texts stay exactly as you pasted them.

إنّ الامتحانَ يومَ الأحد

ان الامتحان يوم الاحد

ان الامتحان يوم الاحد
ان الامتحان يوم الاحد

The comparison options do not cover taa marbuta or alef maqsura, so that difference still shows:

تبدأ الدراسة يوم الأحد

تبدا الدراسه يوم الاحد

تبدا الدراسة يوم الاحد
تبدا الدراسه يوم الاحد

To ignore it as well, normalize both texts in the Cleaner first with ة → ه and ى → ي, then compare the normalized copies. The section Texts used for comparison shows exactly what was compared. Each text can be up to 5,000 units long.

A search column in Excel or Google Sheets

If your names live in a spreadsheet, add a new column, call it something like search key, and leave the original column untouched. There are two ways to fill it.

Normalize the column in the Cleaner

1. Copy the cells of the column and paste them into the Cleaner. Each cell arrives as its own line.
2. Tick the normalization and diacritics options you need.
3. Leave Remove blank lines, Join lines with a space, line sorting and duplicate removal off. They change the number or order of lines, and your rows would no longer line up.
4. Copy the result and paste it into the new column, starting on the same row. Check that the last row still sits next to its name.

If some cells contain line breaks, the rows will not line up this way; use the formula instead.

Or use SUBSTITUTE

Excel and Google Sheets both have SUBSTITUTE with the same arguments: the text, what to look for, and what to put instead. Leave out the occurrence number and every occurrence is replaced. With names in column A starting on row 2, type this in B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"أ","ا"),"إ","ا"),"آ","ا"),"ى","ي"),"ة","ه")

Then fill it down. The formula only handles those five letters and does not strip diacritics, so for vowelled text use the Cleaner method. If your formulas use semicolons between arguments, use them here too. Then search the key column with a normalized term such as احمد.