Splitting out the surname

[bxcfilm]

I have been provided with a spreadsheet with one column, a list of names. I want to separate out the surname into a separate column, but the format in which the names are written is not consistent. For example:

John Smith
James Edward Brown
Mr Michael Q Evans

In each case, the surname is the last word in the name, but Text To Columns doesn't seem to cope with this. I would appreciate any suggestions.

 

[nardagus]

Hi,

First of all. This is not my idea. I saw this many years ago, but tbh I don't remember now who was it and can't provide a link:

=TRIM(RIGHT(SUBSTITUTE(A9," ",REPT(" ","99")),100))

 

[GraH]

If you'd have Excel 2016 or higher, without formula, in case this is a one shot
- Type in a new column for each scenario a single example (thus typing the surname)
- Then highlight the full range in that column
- Press CTRL+E (flash fill)

 

[bxcfilm]

Hi,

First of all. This is not my idea. I saw this many years ago, but tbh I don't remember now who was it and can't provide a link:

=TRIM(RIGHT(SUBSTITUTE(A9," ",REPT(" ","99")),100))

That works a treat! Many thanks.

 

[alansidman]

In Power Query you can split the column by using a space delimeter for the right most space.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"

Book1
	A	B
1	Column1.1	Column1.2
2	John	Smith
3	James Edward	Brown
4	Mr Michael Q	Evans
Sheet2

 

[nardagus]

That works a treat! Many thanks.

You're welcome.