When importing data from external sources or pasting large volumes of information into Excel, it's common for the data to be placed in a single column. This can make the data difficult to work with, especially when you need to separate it for in-depth analysis. By converting the text into multiple columns, you can create a clearer structure that allows for easier sorting, filtering, and analysis. In this article, we will introduce how to convert text to multiple columns in Excel in Python using Spire.XLS for Python.
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Convert Text to Multiple Columns in Excel in Python
Spire.XLS for Python does not offer a direct method for converting text in a cell into multiple columns. However, you can accomplish this by first retrieving the cell content using the CellRange.Text property. Next, use the str.split() method to split the text based on a specified delimiter, such as a comma, space, or semicolon. Finally, write the split data into individual columns. The detailed steps are as follows:
- Create an object of the Workbook class.
- Load an Excel workbook using the Workbook.LoadFromFile() method.
- Access a specific worksheet using the Workbook.Worksheets[index] property.
- Loop through each row in the sheet.
- Get the content of the first cell in the current row using the CellRange.Text property. Next, split the content based on a specified delimiter using the str.split() method, and finally, write the split data into separate columns.
- Automatically adjust column widths in the worksheet using the Worksheet.AllocatedRange.AutoFitColumns() method.
- Save the modified workbook to a new file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Specify the input and output Excel File paths
inputFile = "Template.xlsx"
outputFile = "ConvertTextToColumns.xlsx"
# Create an object of the Workbook class
workbook = Workbook()
# Load the Excel file
workbook.LoadFromFile(inputFile)
# Get the first worksheet in the file
sheet = workbook.Worksheets[0]
# Loop through each row in the worksheet
for i in range(sheet.LastRow):
# Get the text of the first cell in the current row
text = sheet.Range[i + 1, 1].Text
# Split the text by comma
splitText = text.split(',')
# Write the split data into individual columns
for j in range(len(splitText)):
sheet.Range[i + 1, j + 2].Text = splitText[j]
# Automatically adjust column widths in the worksheet
sheet.AllocatedRange.AutoFitColumns()
# Save the modified Excel file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
