Skip to main content

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!

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.

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.

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},

    "[^ ]+$"

  )

)

Reply


Terms & Conditions