Get surnames from a String

Hey there,

I have a “challenge” that I have been thinking about for one week now.

I have an excel, which contains some columns, one of them is “Name”, where I get a String in a cell from the name and the Surname, I.E: Andrea Johnson

Whaty I want, is to find some of way to separate names from surnames. You can think is easy because most of the people have only one surname, so you could just say last substring is surname and first is name, but for example, in Spain we have 2 surnames, and some surnames even contain more than one word for surname I.E: Andrea Johnson De Santamaría

Any ideas on how to get that? Looks kinda difficult tbh, I tried some regex expressions like this but nothing seemed to help me

/^[a-zA-ZàáâäãåąčćęèéêëėįìíîïłńòóôöõøùúûüųūÿýżźñçčšžÀÁÂÄÃÅĄĆČĖĘÈÉÊËÌÍÎÏĮŁŃÒÓÔÖÕØÙÚÛÜŲŪŸÝŻŹÑßÇŒÆČŠŽ∂ð ,.’-]+$/u

Any help? Thanks in advance!!!


Why don’t you try using an split to get the name.

FirstName=Split(Name," ")(0) -> In this way, you would get “Andrea”

Then, you take the String name and replace the FirstName for “”,


And then you would get Johnson de Santamaría.

There could be a problem with people with first and second name.

Does this solution fits your requires?


Hello Daniel,

Thanks for your reply!!! Sounds pretty good, but yeah, the problem are people with a second name… Any way to solve that? I need it to be as exact as possible

I would use the Split and a counter.

If Split(Names, " ").Count = 3, then I would take the two last names.

If Split(Names, " ").Count = 4 then I would take the two last names (2 last strings)

If Split(Names, " ").Count > 4 I would say that it is a “Composite last name” such as Ruiz de Azua or Gonzalez de Mendoza.

It mostly depends on the characteristics of your population.

If you have another doubt, don’t hesitate in telling me!

Kind regards,

1 Like


I think that with what you said plus more thinking I can manage to do it!!! Bravo :smiley:

Have a wonderful day ^^

1 Like