Very special occurence count

Hi All,
I'm in the process of analyzing a pretty large data sheet of two columns (A &B) and over 8000 rows of data.

ColA carries all the data strings in its cells. Data strings vary in length which could reach 32000 chars per cell.

ColB has very particular words and phrases that pertain specifically to the contiguous cells. So B1 has words and phrases from A1, and B30 has also words and phrases from A30 and so forth.

BolB words and phrases also vary in length from 6 to 400 chars.

My task is to get a 100% accurate count of occurence of every cell in ColB from its neighbour in ColA.

To do that I directly used this formula in ColC:

Please Login or Register  to view this content.

yielding the occurence count of ColB strings from ColA.

The job would've been finished if the data was in English. It is rather in Arabic, with its full diacritical marks in both ColA and ColB.

I tried normalizing the text by removing all the diacritical marks, and this actually worsened the problem as the text lost its meaning when the diactritical marks were removed. The formula yielded totally wrong count for words that looked the same as normalized, but they are in entirely different words and meanings with their diacritical marks on.

To solve this problem, I need to run a smart script that uses dictionary to record all the ColA and ColB data "as they are" with their diacritical marks, and run its smart logic to detect the occurence of every cell in ColB in its contiguous cell of ColA, yielding the count in ColC respectively.

Unfortunately I cannot send a data sample of that file and will be really difficult to try to make up an example, this is why I had to explain my problem in detail.

Can I get some precious assistance with this problem?

Always, many thanks in advance.

T.

Substitute worked fine with normalized Arabic text, and the formula did count the occurrence of the text from ColB in ColA, but it wasn't right at all. I tried to use UPPER with original text with diacritical marks, but of course it didn't make any difference.
Thanks shg.

"Wasn't right at all" -- what does that mean?

It meant that the occurrence in ColA for the text in ColB was not correct. Once the text is normalized it turns to be basic, and the difference in meaning between two same words with same letters but different diacritical marks becomes eliminated. So, for example, instead of counting 6 for the occurrence of a word with diacritical marks, it would be 10 without diacritical marks due to stripping out the differences between words of the same letters.

Yes, ColA and ColB are consistent.

I hope I made it much clearer.

I think we'll be going the wrong way if we looked into the Arabic text as text not as bits. In other words, we should be dealing with it as if it is English, but using scripting dictionary.