Text to Columns Update/ Replace Formula

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

Accepted answers

@Masond3 in a Formula tool, insert the following

First Name

GetWord([Name], 0)

Last Name

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

@Masond3 in a Formula tool, insert the following

First Name

GetWord([Name], 0)

Last Name

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

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

Screenshot 2024-04-23 154652.png

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

No worries, @Cremo! Great minds think alike.

@Masond3 Another approach