CODEWITHSUNDEEP
excel
thegunner
thegunner

Reputation: 7163

Remove text from excel columns with certain criteria

I have some excel rows and some of them contain two names, but I want to completely get rid of the second name if it exists. E.g.

Tom Jones                           ok
Michael Jones and Helen Smith       not ok
Michael Bloggs and Gemma Jones      not ok
John Murphy and Hannah Murphy       not ok
Jane Brown                          ok

Is there a formula to get rid of the second name if the column (if they have a second name)?

Anybody know?

Upvotes: 0

Views: 47

Answers (4)

VBA Pete
VBA Pete

Reputation: 2666

Yes, you can use a find formula such as:

=IFERROR(LEFT(A1,FIND(" and ",A1,1)-1),A1)

enter image description here

Upvotes: 1

Jonathan
Jonathan

Reputation: 1025

Combine LEFT and FIND into a helper column:

=LEFT(A1,FIND(" and",A1)-1)

Then copy and paste values over your old data.

Upvotes: 0

mojo3340
mojo3340

Reputation: 549

Try this:

=LEFT(A1,LEN(A1)-FIND("and ",A1))

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

If having and means there's going to be a second name, you can put this in a new column (say C1), and drag down. It'll return only the first name:

=IFERROR(LEFT(A1,SEARCH(" and ",A1)),A1)

Note this will allow you to have a name, such as Randy Jones and it won't cut it out, but it'll keep Randy Jones from the names Randy Jones and Mary Poppins.

Upvotes: 0

Related Questions