In various cases, you may need to split text in a column into multiple columns in an Excel worksheet. The splitting criteria could be a blank space, a comma, a special character, etc. In this article, you will learn how to split a text to columns in an Excel file using Python. After reading this article, you will be able to perform Excel’s Text to Columns in Python.
Excel Text to Column in Python - API Installation
In order to split text in one column into multiple columns in an Excel worksheet, we’ll use Aspose.Cells for Python. It is a powerful and feature-rich API that lets you create, modify and convert Excel files using Python. You can install the API using the following pip command.
pip install aspose-cells
Split Excel’s Text to Columns in Python
The following are the steps to split text to columns in Excel using Python.
- First, load the Excel file using the Workbook class.
- Get the reference to the worksheet where you want to split the text.
- Add values to the cells (optional).
- Create an instance of TxtLoadOptions class and specify the splitting character using TxtLoadOptions.setSeparator(value) method.
- Split text to column using Worksheet.getCells().textToColumns(int row, int column, int totalRows, TxtLoadOptions options) method.
- Finally, save the Excel file using Workbook.save(fileName, SaveFormat.XLSX) method.
The following code sample shows how to perform Excel text to columns in Python.
Output
Python API for Excel Text to Column - Get a Free License
You can get a free temporary license to use Excel text to column feature without evaluation limitations.
Conclusion
In this article, you have learned how to perform Excel text to columns in Python. More precisely, you have seen how to split the text in a column into multiple columns in Excel using Python.
Python Excel API - Read More
You can explore more about Aspose.Cells for Python using the documentation. In case you would have any queries, contact us via our forum.