Suppose you have a list of full names. Those names may or may not contain middle names. Now, you want to extract first, middle and last names out of those full names. Here is how.
First, separate full names to columns with space.
Make sure to separate names into 3 columns In the ‘Separate to Column’ dialog.
Then, you get 3 columns.
For names with middle names, the 1st column has first names, the 2nd column has middle names and the 3rd column has last names.
For names without middle names, the 1st column has first names, the 2nd column has last names and the 3rd column has NAs.
We can just use the 1st column for first names. For middle names. we want to create a new column based on the 2nd column and set NA if the value in the 3rd column is NA
From the column header menu of the 2nd column, choose ‘Replace Values’ and ‘By Setting Conditions’.
Set ‘middle_name’ for the new column name and set a condition like following.
case_when(
!is.na(name3) ~ name2,
TRUE ~ as.character(NA)
)
Now, we have middle names in ‘middle_name’ column.
For last names, we want to create a new column based on the 3rd column and set the value of the 2nd column if the value is NA.
From the column header menu of the 3rd column, choose ‘Work with NA’ and ‘Fill Na with Other Columns’.
Set ‘last_name’ for the new column name and set ‘name2’ for the other column like the following.
Now, we have columns for first, middle and last names.
