# Re: How to divide a record into different ones

Solved
1155 0
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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?

1 Solution

Accepted Solutions
18 - Pluto

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},
"[^ ]+\$"
)
)
``````
3 Replies 3
18 - Pluto

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.

12 - Earth

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.

18 - Pluto

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},
"[^ ]+\$"
)
)
``````