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

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.

CodePudding user response：

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:]:
    firstname, lastname = cell.value.split()
    ws.cell(row=cell.row, column=firstname_column).value = firstname
    ws.cell(row=cell.row, column=lastname_column).value = lastname

# 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: