Find a vowel and double the consonant before it in a string in one cell

med121med

New Member
Joined
Nov 22, 2004
Messages
2
Hi to all
I am trying to build a formula that will look for a vowel in a string in the same cell and when found I want to change all its instances to another vowel then I want to double the consonant before each instance of this vowel. This is mainly to deal with Diacritics of Arabic text.


Arabic Vowels: ---------------UNICODE:
  • shada [size=+4]ّ[/size] ---------------- 1617----------------stressed, like "stress on p in problem"
  • sukoun [size=+3] ْ [/size] ------------ 1618 ---------------- no vowel
  • fatha [size=+3]َ [/size] ---------------- 1614 ---------------- a
  • kasra [size=+3]ِ [/size] ---------------- 1616 ---------------- i
  • dama [size=+3]ُ [/size] ---------------- 1615---------------- o


Example in B12: I have this sentence:
[size=+8]مَدَّ الظِّلَّ مَدّا[/size]
(mada athela madan) meaning "He streched the shade." [th] in arabic is one sound

Using the following formula:

=SUBSTITUTE($B$12,MID($B$12,SEARCH(shada,$B$12),1),sukoun&REPT(MID($B$12,SEARCH(shada,$B$12)-1,1),1))

I want each shada (the 3 like shape to be replaced with sukoun (the little o) and every main letter bellow it to be doubled so that I get the following output:
[size=+4]
مَدْدَ
الظْظِلْلَ
مَدْدا
[/size]

This is basically like decompressing a stressed letter to its two main sounds like [size=+3]دّ[/size] to [size=+3]دْد[/size] or [size=+3]ظِّ[/size] to [size=+3]ظْظِ[/size] or [size=+3]لَّ[/size] to [size=+3]لْلَ[/size]

So far with the formula above this is the output I am getting
[size=+4]
مَدْدَ الظْدِلْدَ مَدْدا
[/size]


As you can notice only the first instance of the shada is substituted to sukun ie no vowel and only the character before it gets doubled and gets carried to the following shada positions in the following words.

In the formula above I used the following named ranges.

  • C2 named shada FORMULA =UNICHAR(1617)
  • D2 named sukoun FORMULA =UNICHAR(1618)
  • E2 named fatha FORMULA =UNICHAR(1614)
  • F2 named kasra FORMULA =UNICHAR(1616)
  • G2 named dama FORMULA =UNICHAR(1615)

I would be very grateful if someone could shed some light on this problem either in a formula form or user defined function and thanks to you all in advance.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
السلام عليكم ورحمة الله وبركاته
just quick bump
I would like make same formula for creating tashrif all wazan in shorof and for i'rob in nahwu but still stuck
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top