Separation of compound names, within the name

[shimaa01234]

Can you separate the compound names in Arabic
Such as:
Abdel Rahman, Abdel Massih
Using the formula
Separation of compound names, within the name,
Name the compound so that, in a single column
And the rest, all in the name of a separate column
Like this table

Mohammed Abdel Rahman Bayoumi	Mohammed	Abdel Rahman	Bayoumi
Abdel Rahim Mustafa Tayeb	Abdel Rahim	Mustafa	Tayeb
Elias Abdel Massih Charles	Elias	Abdel Massih	Charles

<tbody>
</tbody>

 

[István Hirsch]

First - I'm sorry for your trouble
Secondly - the expected result is:

Abd Elrahman Abd Elaziz Mohammed Ibrahim

Abd Elrahman

Abd Elaziz

Mohammed

Ibrahim

These two formulas changed:

C1:
=IF(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),FIND(" ",TRIM(SUBSTITUTE(A1,B1,"")))-1)<>"Abd",LEFT(TRIM(SUBSTITUTE(A1,B1,"")),FIND(" ",TRIM(SUBSTITUTE(A1,B1,"")))-1),TRIM(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(A1,B1,""))," ",REPT(" ",99)),198)))

D1:
=IF(ISNUMBER(FIND("Abd",TRIM(SUBSTITUTE(A1,B1&" "&C1&" ","")))-1),TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,B1&" "&C1&" ","")," ",REPT(" ",99)),198)),TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,B1&" "&C1&" ","")," ",REPT(" ",99)),99)))

Excel Workbook
	A	B	C	D	E
1	Abd Rahman Samir Ali	Abd Rahman	Samir	Ali
2	Abd Allah Ibrahim Marzouki	Abd Allah	Ibrahim	Marzouki
3	Samira Syed Abd ElMomin	Samira	Syed	Abd ElMomin
4	Alaa ElDin Mohamed Ali	Alaa ElDin	Mohamed	Ali
5	Peter Michel Abd ElMassih	Peter	Michel	Abd ElMassih
6	Abd Elaziz Ibrahim Abd Elrahman	Abd Elaziz	Ibrahim	Abd Elrahman
7	Abd Elaziz Ibrahim Abd Elrahman Mohammed	Abd Elaziz	Ibrahim	Abd Elrahman	Mohammed
8	Abd ElRahman Abd ElAziz Mohammed Ibrahim	Abd ElRahman	Abd ElAziz	Mohammed	Ibrahim
Sheet

 

[shimaa01234]

You are a wonderful
All words of thanks, not enough to express my thanks to you
You are a wonderful person
Thank you
Everything works wonderfully

 

[István Hirsch]

You are welcome, thanks for the feedback.

 

[István Hirsch]

P.S.

Continuous adapting to newer and newer name structures has made the formulas longer and longer. Now, looking through the name patterns available, easier processing methods come to mind. One of them may be the following:

(If you want to keep the names in their original form, copy/past them into another column, for example, if you want to create a table like the one in post #31, copy/paste colum A to column B.)

Select the names to process
Go to Edit/Replace and replace all „ „ with „;” (that is, replace space with semicolon) ( without the quotation marks)
Then replace all „Abd;” with „Abd „ (Abd and space) and if Alaa has the same function in the names as Abd then also replace all „Alaa;” with „Alaa „ (Alaa and space)
Go to Data/Text to Columns and check „Delimited” radio button then click „Next”
In the next window check „Semicolon” and click „Finish”

 

[shimaa01234]

Thank you very much Mr. "Istvan"
In order to, this information
But, I'm sorry
Formulas actually work well
But there are some names, do not work well without repeating space before
Like this:

Mahmoud Khalifa Abd ELLatif Abd elBary Shows like that: Mahmoud Khalifa Abd ELLatif Abd elBary Mahmoud Khalifa Abd ELLatif Abd elBary <colgroup><col width="296"><col width="85"><col width="71"><col width="94"><col width="146"></colgroup><tbody> </tbody>

<colgroup><col width="296"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

@shimaa01234
Please check your Private Messages

 

[István Hirsch]

Thank you very much Mr. "Istvan"
In order to, this information
But, I'm sorry
Formulas actually work well
But there are some names, do not work well without repeating space before
Like this:

Mahmoud Khalifa Abd ELLatif Abd elBary Shows like that: Mahmoud Khalifa Abd ELLatif Abd elBary Mahmoud Khalifa Abd ELLatif Abd elBary

Formulas in post #31 will be discontinued, as they have become too long, too hard to further develop.
The good news is that the new name can be processed correctly by the method described in post #34, supposing Mahmoud*Kalifa*Abd Ellatif*Abd elbary is the correct separation.

Also, the macro below gives the same result (inserting macros is described in many posts on these pages):

Sub Sepnames()
Dim c As Range
Dim lc, uc As String
'For example:
lc = "a21"   'First cell
uc = "a29"   'Last cell
        For Each c In ActiveSheet.Range(lc, uc)
          c.Offset(, 1) = Replace(Replace(Replace(c, " ", ";"), "Abd;", "Abd "), "Alaa;", "Alaa ")
        Next
    Application.DisplayAlerts = False
    Range(lc, uc).Offset(, 1).Select
    Selection.TextToColumns Destination:=Range(lc).Offset(, 1), DataType:=xlDelimited, _
    semicolon:=True
    Application.DisplayAlerts = True
End Sub

Excel Workbook
	A	B	C	D	E
21	Abd Rahman Samir Ali	Abd Rahman	Samir	Ali
22	Abd Allah Ibrahim Marzouki	Abd Allah	Ibrahim	Marzouki
23	Samira Syed Abd ElMomin	Samira	Syed	Abd ElMomin
24	Alaa ElDin Mohamed Ali	Alaa ElDin	Mohamed	Ali
25	Peter Michel Abd ElMassih	Peter	Michel	Abd ElMassih
26	Abd Elaziz Ibrahim Abd Elrahman	Abd Elaziz	Ibrahim	Abd Elrahman
27	Abd Elaziz Ibrahim Abd Elrahman Mohammed	Abd Elaziz	Ibrahim	Abd Elrahman	Mohammed
28	Abd ElRahman Abd ElAziz Mohammed Ibrahim	Abd ElRahman	Abd ElAziz	Mohammed	Ibrahim
29	Mahmoud Khalifa Abd ELLatif Abd elBary	Mahmoud	Khalifa	Abd ELLatif	Abd elBary
Sheet

 

[shimaa01234]

Excellent code, Mister "Istvan"
But I was, preferred formula
Because I can be used better

 

[István Hirsch]

Excellent code, Mister "Istvan"
But I was, preferred formula
Because I can be used better

If using formula is preferred, give this a try. To make it a bit more comfortable, enter this formula in B1 and just copy down and right as long as there are names (if there are many rows, just click on the filling plus appearing in the right bottom corner of B1). Hope this will work even if further names are added of the similar structure.

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&$A1,"Abd ","Abd,"),"Alaa ","Alaa,")," ",REPT(" ",99)),COLUMN(A$1)*99,99)),","," ")

(Result as in post #37)

 

[shimaa01234]

Yes, you did it
You are really wonderful
Thank you very much that I wanted from the beginning
Has been achieved
Thank you very much