# Clean Up Messy Excel Spreadsheets with AI

> Learn how to use AI like ChatGPT and Claude to split columns and format messy data into clean Excel tables in seconds, complete with practical prompt templates.

- Author: Nguyễn Ngọc Độ
- Published: 2026-10-19
- URL: https://nguyendo.id.vn/en/blog/clean-up-messy-excel-spreadsheets-with-ai

## Key takeaways

- AI can convert unstructured text into accurate tabular data without requiring advanced knowledge of complex formulas or Macro/VBA.
- Data security is the top priority: always mask or remove personally identifiable information (PII) before feeding data into AI tools.
- Instead of uploading entire Excel files to AI, the safest and most effective method is having AI write formulas for you to apply locally.

Artificial intelligence (AI) such as ChatGPT or Claude can transform messy data from chat messages, emails, or legacy software exports into neat spreadsheets in just seconds. Instead of tediously copying row by row or spending hours searching for complex formulas, you simply describe your requirements in natural language, and AI will automatically categorize, split columns, or generate ready-to-use formulas for Excel/Google Sheets.

At GoMetap, our O2O ecosystem automates data synchronization from POS systems to Apple/Google Wallet membership cards to keep everything seamless. However, I understand that in daily operations, business owners and office staff still frequently handle messy raw data from suppliers, customer feedback, or legacy systems. Here is how to resolve this quickly and efficiently.

## What Is Data Cleaning and Why Is AI So Good at It?

**Data Cleaning** is the process of detecting, correcting, and reformatting inaccurate, incomplete, or disorganized information within a dataset to bring it to a standardized format ready for analysis.

Previously, if you copied a customer list from Zalo messages or a PDF and pasted it into Excel, everything often ended up stuck in a single column. To split names, phone numbers, and addresses into separate columns, you had to be proficient with the Text to Columns tool or write cumbersome string-splitting formulas (Left, Right, Mid). Today, AI with natural language processing (NLP) capabilities can instantly recognize names versus phone numbers and reorganize them without requiring any mathematical formulas.

## Vital Note: Enterprise Data Security

When using AI to process data, the ultimate rule for business operators is never to leak customer information or confidential financial figures.

**PII (Personally Identifiable Information)** refers to information that can be used to directly identify a specific individual, such as a full name, phone number, national identity number, or home address.

*   **Do not upload raw files containing PII to ChatGPT/Claude:** Unless you use an Enterprise version (which guarantees data is not used for model training), never upload real customer lists.
*   **Solution:** Replace real names with "Customer A", "Customer B"; mask phone numbers (e.g., change to 090xxxx123), or copy just 5 rows of anonymized sample data so the AI can write formulas for you to run locally.

## 2 Sample Prompts to Turn Messy Data into Clean Tables

### Prompt 1: Split Messy Text into a Table

Suppose a sales rep sends you an order list copied from chat messages, with everything jumbled up without any structure. Simply mask sensitive information and use the following prompt:

```text
Đóng vai một chuyên gia phân tích dữ liệu. Tôi có một đoạn văn bản chứa thông tin đơn hàng bị lộn xộn dưới đây. 

Hãy trích xuất thông tin và trình bày lại dưới dạng bảng (Table) với các cột sau:
1. Mã khách hàng (Tạo mã tự động từ KH01, KH02...)
2. Tên người nhận
3. Sản phẩm đặt mua
4. Số lượng
5. Ghi chú (Nếu có)

Yêu cầu bổ sung:
- Tự động sửa các lỗi chính tả ở tên sản phẩm nếu phát hiện sai sót (ví dụ: "ca phe" thành "Cà phê").
- Dữ liệu thô:
[Dán dữ liệu đã ẩn danh của bạn vào đây]
```

**Expected outcome:** AI will return a neatly structured Markdown table. Simply highlight the entire table with your mouse, press Ctrl+C, switch to Excel, and press Ctrl+V—the table will populate accurately across individual columns.

### Prompt 2: Have AI Write Excel Formulas to Standardize Data

Instead of feeding data to AI, the most secure approach is to ask AI to generate formulas that you run yourself.

For example: Column A contains phone numbers entered inconsistently: with spaces (090 123 4567), with a country code (+84901234567), or with dots (090.123.4567).

```text
Tôi đang dùng Excel. Ở cột A, từ ô A2 trở xuống, tôi có một danh sách số điện thoại nhập lộn xộn, bao gồm khoảng trắng, dấu chấm, dấu gạch ngang và mã vùng +84.

Hãy viết cho tôi MỘT công thức Excel kết hợp để chuẩn hóa dữ liệu này tại ô B2 với các điều kiện sau:
1. Loại bỏ toàn bộ khoảng trắng, dấu chấm, dấu gạch ngang.
2. Nếu số bắt đầu bằng "+84", hãy thay thế nó bằng số "0".
3. Trả về định dạng chuỗi văn bản (text) giữ nguyên số 0 ở đầu.

Hãy giải thích ngắn gọn từng bước của công thức để tôi có thể tự điều chỉnh khi cần.
```

**Expected outcome:** AI will return a combined formula (for example, using `SUBSTITUTE`, `REPLACE`, `IF`) along with a clear explanation. Simply copy and paste it into cell B2 and drag down—your customer data never leaves your computer.

## Comparison of AI Tools for Data Processing

If you are deciding which tool to use, here is a quick assessment from my perspective:

| Feature / Tool | ChatGPT (GPT-4o) | Claude (3.5 Sonnet) | Google Gemini Advanced |
| :--- | :--- | :--- | :--- |
| **Generate tables from text** | Very good | Outstanding (Fewest errors) | Fairly good (Occasionally skips rows) |
| **Write complex Excel formulas** | Good, easy-to-understand explanations | Highly intelligent, strong logical reasoning | Best suited for Google Sheets |
| **Analyze files (.csv, .xlsx)** | Advanced Data Analysis executes code directly | Can read files and process them ultra-fast | Fast reading, natively integrated with Workspace |

## 4 Steps to Take Action Today

To start streamlining your data operations, follow these 4 steps:
1. **Choose test data:** Open an old Excel file or the messiest message thread you have.
2. **Anonymize:** Remove real name and email columns immediately, replacing them with dummy data.
3. **Open Claude or ChatGPT:** Copy Prompt 1 above and paste your data in.
4. **Copy back to Excel:** Once you see the output, copy and paste it into Excel. If columns do not align properly, ask the AI: *"Reformat this table so that when I copy and paste it into Excel, the columns do not merge."*

Applying AI to these small tasks is the first step toward building an automation and digitalization mindset across your business, saving your team hundreds of tedious manual work hours each month.