Text to columns 3 columns need only 2

[bobkap]

I have a list of names where both first and last name is separated by a comma. I need to have the last name in one column and the first in another. I think I've figured out how to do that with this code below. Except I cannot figure out how to address this:
1. If the person's first name is two words (ex.: Billy Joe; or Bobby Sue etc) I end up with 3 columns, not 2. I thought the "consecutive delimeter" would address that but it does not. Might anyone know how to handle that please so I end up with just 2 columns?

 

[alansidman]

With Power Query/Get and Transform Data found on the Data Tab

Book1
	A	B	C	D
1	Name		Name.1	Name.2
2	Billy Bob Thornton		Billy Bob	Thornton
3	Bobby Sue Smith		Bobby Sue	Smith
4	James Jones		James	Jones
5	Amit Patel		Amit	Patel
6
Sheet1

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

 

[Peter_SSs]

Duplicate to: Text to columns to 3 columns

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

If there are follow-up questions then please continue in the linked thread.