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 into columns in an Excel worksheet using Python. It automates the Text to Columns feature of MS Excel.
Python API for Excel’s Text to Columns
In order to split text in one column into multiple columns in an Excel worksheet, we’ll use Aspose.Cells for Python via Java. 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 Text to Columns in Excel using 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’s text to columns operation in Python.
Output
Get a Free API License
You can get a temporary license in order to use the API without evaluation limitations.
Conclusion
In this article, you have learned how to split text to columns in Excel using Python. More precisely, you have seen how to split the text in a column into multiple columns in Excel using Python. You can explore more about Aspose.Cells for Python via Java using the documentation. In case you would have any queries, contact us via our forum.
