Excel Functions not Differentiating Some Arabic Letters

user_1317499 0
Viewed 513

I have a table in excel with some Arabic letters/words with their id numbers; that is a table of two columns as such:

ID          Letter
ء           1
2           آ
3           أ
4           إ
5           لا
6           لإ
7           ئ
8           ا
9           ـا
10          لأ
11          ـلا
12          ب
13          بـ
14          ـب
15           ـبـ

What I want is to be able to retrieve the id of the letter after I enter the letter in another sheet. I have used both the match and vlookup excel functions and they seem to work fine. However, the problem is that it does not identify the letters correctly. 

So, for example, if I enter ء I get 1 - which is correct.
If I enter آ I get 2 - which is correct.
If I enter أ I get 2 - which is incorrect, it should return 3
If I enter إ I get 2 - which is incorrect, it should return 4
If I enter ب I get 12 - which is correct.
If I enter بـ I get 13 - which is correct.
If I enter ـب I get 13 - which is incorrect, it should return 14
If I enter ـبـ I get 12 - which is incorrect, it should return 15

The formula I am using for the vlookup:

=VLOOKUP(A2; Full!$A$3:$E$145; 2; FALSE)

where: A2 has the letter I enter Full!$A$3:$E$145 is the table with my data 2 is the number of the column I want to get the data from False because I want an exact match

What could be the problem?

vlookup arabic match excel-formula excel
2 Answers

I'm sure someone smarter will come up with something more elegant, but here is a kludge that works for any sample 3 or fewer characters long:

  1. Add a sixth column to your table populated with the following formula:

    =CHOOSE(LEN(B2),UNICODE(B2),UNICODE(B2) & UNICODE(MID(B2,2,1)),UNICODE(B2) & UNICODE(MID(B2,2,1)) & UNICODE(RIGHT(B2,1)))

(Where I assumed column B is where the Arabic text is in your table)

  1. Then use the following formula to get the row in the table:

    =MATCH(CHOOSE(LEN(A2),UNICODE(A2),UNICODE(A2) & UNICODE(MID(A2,2,1)),UNICODE(A2) & UNICODE(MID(A2,2,1)) & UNICODE(RIGHT(A2,1))),Full!F$2:F$16,0)

(where A2 is where you enter the looked for text and Full!F$2:F$16 is the column with the formula inserted in step 1)

Once you have a match, you can use INDEX to retrieve whatever data you need.

user_82604840

You can use the UNICODE() formula to provide an id that can be compared properly.

I've created a new column A on the Sheet "Full" (id is now moved to column B and Arabic letters to column C), formula on column A is as follows for cell A25 in the sheet Full:

=UNICODE(C25)

Then the formula to retrieve the id would be:

=VLOOKUP(UNICODE(A2); Full!$A$3:$B$145; 2; FALSE)

where A2 is where you put the arabic letter you want to know the id.

user_38138460
Related