Delete fifth Name

Formula:

Please Login or Register  to view this content.

Regards, 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.

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

I have no idea what that means

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

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)))

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?

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

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

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

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))

Given this list of names:

HTML Code: 

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.