Create new column based on last space delimiter of existing column?

Hi all,

I came across this problem and I've been wondering how to solve it. I have a table that looks a little bit like this:

FirstNameLastNameMiddleName(actually all 1 column)

John Doe Johnson

Mary Louise

Anthon Oparin Gilbert

I would like to separate these into a new column, at the last space delimiter, meaning it should look like this:

New Column:

Johnson

Louise

Gilbert

Is this possible? Thanks for your help.

Developer

Expression

Accepted answers

Ah, I see - I thought from the example that there were multiple spaces or tabs.

To get the last word from each string use

^.*\s(.*)$

Configured as below

Spoiler

No worries. You were right on the mark with Regex replace - but in this case we want to replace everything except what we wanted to parse out.

As such, the formula below will get the job done (for your challenge.xlsx data).

trim(REGEX_Replace([First Name], '^(.*)\s.*$', '$1'))

If there are 2 or more spaces we can edit it to become

trim(REGEX_Replace([First Name], '^(.*)\s{2,}.*$', '$1'))

Both of these get configured in the formula tool as below

Spoiler

Hi AkisM,

This can be done via the RegEx tool.

If the names are all separated by multiple spaces then you can use

\w+\s{2,}\w+\s{2,}(.*)

If the names are separated by a tab then you can use

\w+\t\w+\t(.*)

These expressions are placed in the RegEx tool configured as below.

Spoiler

Hi and thanks for the response, unfortunately the RegEx above does not work.