Extracting surname from a cell

Help
1

Hi.

i am looking to extract the surname form a cell in a spreadsheet. The cell will have mixed data e.g John Harold Smith 485734953
but there would also be time when it will be John Smith 453459 or J Smith 435973 so i’ve used an array and used .split (" ") to capture the first name but is there a formula thta i could use to capture the surname? as it stands i can’t use MyArr(1) as it will take the middle name for some of the rows.

can anyone help me here?

2

@nick.v,

I’m just thinking can we use Regex here, so you would first find the number and then look back to find alphabets until the while space, that is if it never happens that the data could look like this for example “Smith J 448545”

3

@SenzoD i have used regex to get the name from the cell i used
Regex.Match(Variable, “(.*)(?=\sWR)”)

the number that comes after the surname starts with WR.
Once ive captured the first middle and surname how do i then extract the surname as MyArr(1) will pick up the middle name on occasions.

4

@nick.v, i think you need to use regex again, or another thing i can think of is.

use MyArr(2), check if it is not empty, if it is not then check if it has digits, if yes that means MyArr(1) has the surname but if not. then repeat the same process for MyArr(1). i don’t know if this makes sense at all?