Can you Split Full names to First name and Last name with Openpyxl?

Question

I have an excel file that I have been trying to split the column(Full name) into 2 separate column of First name and Last name using openpyxl. For example: I have

from openpyxl import Workbook, load_workbook
wb=load_workboo(path)
ws=wb['Sheet1']

Full name: Harry Surf

I want to change to: First Name: Harry Last Name: Surf

Much thanks if you can do this in openpyxl as I wasn't able to find any answers to it.

Answer 1

This is how I'd do it. Load the spreadsheet, get the Full name column, iterate over the cells in that column, split the cell's value, and write the values to 2 new cells on the same row but in different columns.

from openpyxl import load_workbook
import os

filename = r"names.xlsx"
filename_new = r"names (split).xlsx"

wb = load_workbook(filename)
ws = wb.active

# Find the column letter of the "Full name" column, assuming row 1 are headers
for cell in ws[1]:
    if cell.value == "Full name":
        fullname_column = cell.column_letter
        break

# Add the first and last name columns to the end
firstname_column = ws.max_column + 1
lastname_column = ws.max_column + 2

# Write the headers
ws.cell(row=1, column=firstname_column).value = "First Name"
ws.cell(row=1, column=lastname_column).value = "Last Name"

# Iterate over the fullname column, excluding the header row, read the cell's
# value, split it, and write the values to 2 new cells
for cell in ws[fullname_column][1:]:
    fullname = cell.value.split(maxsplit=1)
    try:
        ws.cell(row=cell.row, column=firstname_column).value = fullname[0]
        ws.cell(row=cell.row, column=lastname_column).value = fullname[1]
    except IndexError:
        pass

# Remove the "Full name" column
ws.delete_cols(cell.column)

# Save the spreadsheet to disk
wb.save(filename_new)

# Launch the new file
os.startfile(filename_new)

Input spreadsheet:

Output spreadsheet: