How to divide a record into different ones

[Mariana_Rumuald]

Hi! I have a slight problem that I don’t know how to solve:

I’m working on some lists that need to be divided into different records. In this particular case, the lists that I was given contain the field “name” or “full name” but I need to divide that information into “name”, “middle name”, and “last name”.

So, for example, if I have in one record the name “Amanda N. Pollard”, I need to divide that into
Given name: Amanda
Middle name: N.
Last name: Pollard

The extra complexity comes when not all people within the Company have middle names or when some employees have more than 1 last name. For example: “Jay Martínez Romero” which in this case would look like:
Given name: Jay
Last names: Martínez Romero

Has anybody done this before?

Thanks in advance!

[kuovonne]

There are formula fields that will split apart first, middle, and last names. However, it takes a human to be know the difference between a middle name versus two last names.

[Alexey_Gusev]

Hi,
use something like LEFT({Field},FIND(' ',{Field})-1) for first name
use third parameter of ‘FIND’ to start from some position and condition to check whether it’s middle name or part of last name.
Or, try to find dot or space in MID({Field},FIND(' ',{Field})+1,2), if yes then it’s middle name.
Use substitute 1st and middle name (if exists) from {Field} to get last name.

[kuovonne · Accepted solution]

Here are some formulas that I came up for this use case. Note the need for someone to manually clean up the data.

I recommend creating the formula fields to get the values. Then convert the formula fields to single line text fields, and then do the manual cleanup.

First Name:

IF(
  {Full Name},
  REGEX_EXTRACT(
    {Full Name},
    "^[^ ]+"
  )
)

Middle Name

TRIM(
  REGEX_REPLACE(
    REGEX_REPLACE(
        {Full Name},
        "^[^ ]*",
        ""
    ),
    "[^ ]*$",
    ""
  )
)

Last Name

IF(
  FIND(" ", {Full Name}),
  REGEX_EXTRACT(
    {Full Name},
    "[^ ]+$"
  )
)