Text to Columns Update/ Replace Formula

[Masond3]

Hi All, 

Having a mental block 

I have a column called "Name" within that name field i have up to 5 names being captured all separated by Space.

Name

GARETH MICHAEL MARRINER JOHNSON

GARETH MICHAEL MARRINER

GARETH MICHAEL

GARETH MICHAEL MARRINER JOHNSON MASON

I have performed "text to columns" which has split each name into corresponding new columns. 

I would like to get the first word, and last word of each row to be populated into Columns called (First Name, and Last Name) As shown below 

Name	First Name	Last Name
GARETH MICHAEL MARRINER JOHNSON	GARETH	JOHNSON
GARETH MICHAEL MARRINER	GARETH	MARRINER
GARETH MICHAEL	GARETH	MICHAEL
GARETH MICHAEL MARRINER JOHNSON MASON	GARETH	MASON

Looking forward to your helo 

Many thanks 

Masond3

[rzdodson · Accepted solution]

@Masond3 in a Formula tool, insert the following

First Name

GetWord([Name], 0)

Last Name

GetWord([Name], CountWords([Name])-1)

[Cremo]

Hi @Masond3 

personally I will simply use a formula tool and the "get word" function, as attached. Let me know if it helps you.

 

[Cremo]

Sorry @rzdodson , have not seen you already answered with same solution :)

[rzdodson]

No worries, @Cremo! Great minds think alike.

[binuacs]

@Masond3 Another approach