How to Clean and Transform Data with Excel for Python Automation

1. Introduction

Hello, I’m PiPi. A few days ago a member of the Python community asked about automating office tasks with Python, and I’ll show how to handle the data using Excel.

2. Original and Target Data

The original data and the desired output are shown in the images below.

3. Process

The data is relatively tidy, so we can use Excel’s built‑in tools:

Import the data using the space delimiter.

Apply “Text to Columns” with a comma delimiter.

Use Find and Replace to remove the surrounding parentheses.

Save the result as a new worksheet.

4. Result

The final cleaned data looks like this:

Saving the worksheet completes the task, providing the fan with the required data.

5. Conclusion

This article demonstrated a practical Excel workflow to prepare data for Python automation, and thanked the community members who contributed ideas and code snippets. It also offered tips for asking future questions, such as providing sanitized sample data and attaching error screenshots.