excel formula produces duplicate last names

Hey all,

I have an excel formula producing duplicate last names.

For example, I have a list like this:

Please Login or Register  to view this content.

When there is an ampersand names must get split into two columns, so i use the below formulas:

Please Login or Register  to view this content.

and:

Please Login or Register  to view this content.

This has the resulting effect:

Please Login or Register  to view this content.

As you can see, the formula causes a duplicate of the last name in the first column. Any suggestion to refine formula so it doesn't cause that duplicate, yet doesn't negatively impact the other results?

Thanks for any response

The formula isn't causing that, your data is. How is a formula supposed to determine what pieces of the found string, if any, it's supposed to then go on a "no duplicates" hunt for?

In this rare situation, fix the data.

Put this formula in D15 and copy down, it will see if the first word in B15 is repeated in that cell, and if so give you the corrected string.

=TRIM(IF(ISNUMBER(SEARCH(LEFT(B15,SEARCH(",",B15)-1), MID(B15, SEARCH(",", B15), LEN(B15)))), MID(B15, SEARCH(",", B15)+1, LEN(B15)), ""))

Copy that down the column.
Highlight and copy, then do an Edit > Paste Special > Values to remove the formulas, now you can Ctrl-down-arrow through that column and quickly verify the rows that were flagged as a possible concerns.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.