excel - Remove specific strings only if the names begin with them -

i have cell values containing full names.

i replace/remove following characters whole excel sheet:

al al- el el- 

but fact want them replaced if word start characters. example:

alorfze - (remove "al") aralfzi - (do not remove "al") ibrahim el-ketoob (remove "el-")

moreover, replace characters if matched word has more 4 characters.

replacing text in cells of workbook implies using vba code. put following code in module (use alt-f11 open vba editor, insert--> module add module, copy/paste following text; go worksheet, select "macros", run replacestuff , watch magic. few things note:

1. the use of option explicit : idea, guards against typos etc
2. option compare text : means "al" = "al" - in other words, removes case sensitivity comparison (usually want)
3. the array of strings check variable: makes easy change want edit
4. by setting application.screenupdating = false @ start of macro, , setting true @ end, speed execution , save blinking of spreadsheet during execution
5. using mid() function 2 parameters means "until end of string" - shorter, , more robust, either using "a big number" or len(c.text) - l

edit modified replace search strings if occur @ start of word, not start of cell

option explicit option compare text  sub replacestuff() dim getridof getridof = array("al-", "al", "el-", "el") dim c range dim s, sp dim f integer dim flag boolean  application.screenupdating = false each c in activesheet.usedrange.cells   c.select   if len(c.text) >= 4     each s in getridof       flag = false       f = instr(1, c.text, s)       if f > 0         ' check it's part of 4 letter word or more         sp = instr(f, c.text, " ")         if sp > f + 3 or (sp = 0 , f <= len(c.text) - 3)           if f = 1             flag = true           else             if mid(c.text, f - 1, 1) = " " ' @ start of word               flag = true             end if ' @ start of word           end if ' f = 1         end if ' 4 letter word       end if ' f > 0       if flag         c.value = replace(c.value, s, "")         exit       end if     next s   end if next c  application.screenupdating = true  end sub 

tested:

ibrahim el-ketoob   ibrahim ketoob ibrahimel-ketoob    ibrahimel-ketoob abel        abel aleb        eb al bob      al bob belbo       belbo bol al bel      bol al bel el al       el al allele      lele alele       ele elalle      alle alelle      elle 

note - added string "el-" search string el-ketoob turn ketoob , not -ketoob.

note - things processed "in order". right there exit for statement after first string replaced (this means: "do not compare against other strings in getridof array particular cell; we're done, go next cell"), first match replaced (and 1 match - alfred al-hamsy turned alfred hamsy since al- gets processed before al, , stops. if remove exit for (add apostrophe ' in front of turn comment), turn fred hamsy, alfred alhamsy turn fred alhamsy since first al replaced.

i hope can figure out here how change things fit needs.