Delete fifth Name

[shimaa01234]

I want to separate the first four names without the "title" even if it had been compound , In other words, delete the fifth even if the name "compound name"
Such as "Abd, Abu, Gad"

[TMS]

=IF(ISERROR(LEFT(A2,FIND("^",SUBSTITUTE(TRIM(A2)," ","^",4)))),A2,LEFT(A2,FIND("^",SUBSTITUTE(TRIM(A2)," ","^",4))))

Regards, TMS

[shimaa01234]

You're a genius Mister "TMS"
Thank you very much for this wonderful formula

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[shimaa01234]

I have asked if you want to put a condition Name of the compound under the terms of the formula
How to be formed
Thank you again

[TMS]

if you want to put a condition Name of the compound under the terms of the formula

I have no idea what that means

[shimaa01234]

This formula to separate the last name of the Full Name
Can you make it separating the Full Name without Last Name
=IF(OR(ISNUMBER(SEARCH({"Abd","Abou","Gab","Gad"},RIGHT($A2,LEN($A2)-3)))),TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",255)),255*2)),TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",255)),255)))

[shimaa01234]

Any help to do this formula

[shimaa01234]

Any help please

[daddylonglegs]

Can you give an example of what you want to do, as you did for your first question. Thanks

[shimaa01234]

Is the same question , but I want this formula you delete a name , even if the latter compound

This formula to separate the last name of the Full Name
Can you make it separating the Full Name without Last Name
=IF(OR(ISNUMBER(SEARCH({"Abd","Abou","Gab","Gad"},RIGHT($A2,LEN($A2)-3)))),TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",255)),255*2)),TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",255)),255)))

[daddylonglegs]

OK, are you still trying to do what you asked originally? In which case I'm not sure I understand what that is, can you explain the logic?

[shimaa01234]

I want the formula found in "Test2 " are bringing the first four names
They are bringing Last Name

[daddylonglegs]

Sorry, I know I'm not providing any solutions but I think that if you want a solution to your problem that's long-lasting and robust you need to provide a fuller explanation with a variety of examples.

So far here's a list of things I don't understand

You are talking about "fifth name" and "first four names" but if this list of words need to be discounted

"Abd","Abou","Gab","Gad"

then some of your example have only 3 names, is that right?

Please understand that I don't necessarily understand the details of what you require - I don't know why you are excluding that list, those are titles right?

The formula you quote, i.e.

=IF(OR(ISNUMBER(SEARCH({"Abd","Abou","Gab","Gad"},RIGHT($A2,LEN($A2)-3)))),TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",255)),255*2)),TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",255)),255)))

.....Is showing the last 2 words in the cell if one of "Abd","Abou","Gab","Gad" is present, the last word otherwise.....but that will only work if "Abd","Abou","Gab","Gad" is attached to that last name, with multiple names in the cell I don't see how that wll work.

So, what you should do, is this:

Provide a small but representative list of examples, including all possible types of cases
Without reference to formulas give a verbal explanation of what is required
Also provide an example of what the full output should be

Thanks

[shimaa01234]

Simply,iwant this formula to delete last name

[Speshul]

Formulas don't delete things, they only read things. You can have a formula only show certain things, but it will never delete something.

[shimaa01234]

Well I want the formula to hide last name
I wish to be the same terms as the formula

[Speshul]

Here try this absurd formula

=TRIM(LEFT(A2,FIND(" ",MID(A2,FIND(" ",MID(A2,FIND(" ",MID(A2,FIND(" ",A2)+1,LEN(A2)),2)+FIND(" ",A2),LEN(A2)),(FIND(" ",MID(A2,FIND(" ",A2)+1,LEN(A2)),2)+FIND(" ",A2)))+FIND(" ",MID(A2,FIND(" ",A2)+1,LEN(A2)),2)+FIND(" ",A2),LEN(A2)),1)+FIND(" ",MID(A2,FIND(" ",MID(A2,FIND(" ",A2)+1,LEN(A2)),2)+FIND(" ",A2),LEN(A2)),(FIND(" ",MID(A2,FIND(" ",A2)+1,LEN(A2)),2)+FIND(" ",A2)))+FIND(" ",MID(A2,FIND(" ",A2)+1,LEN(A2)),2)-1))

[shimaa01234]

Rules, which I want to use :
1- I want to hide the fifth name
2. If the the name is preceded by the fifth "abd, abou, gad" is deleted with the fifth Name
3- If the name is composed of the names of four or less left as is

[shimaa01234]

Unfortunately, the formula did not work, I think because I use Office 2003

[TMS]

Given this list of names:

What do you want to get the formula to display ... and what are the "rules" that you apply for that to happen. Forget the formula for the moment. Just explain the logic of selecting the parts of the name you want to display.

And yes, that formula can't work if you are using Excel 2003.