5 Ready-to-Use ChatGPT Prompts for Cleaning Messy Data in Excel

Have you ever received a spreadsheet from a colleague, a form export, or an external source, only to find it riddled with inconsistencies, formatting issues, and scattered information? You're not alone. Professionals in marketing, sales, and HR often spend hours manually standardizing data before they can even begin analysis. This is where chatgpt for data cleaning becomes an indispensable assistant.

Imagine having an intelligent helper that can take your messy data and transform it into a clean, structured format, ready for insights. ChatGPT can do exactly that. As one expert noted, "it can help you with any data cleaning, so if I got some messy data with me and I want to perform some data cleaning task on it, well guess what, ChatGPT will do it for you." It's like having a data expert on call, ready to provide precise Excel formulas or step-by-step instructions. This guide provides five practical data cleaning in excel prompts to get you started.

Stop Wasting Hours on Manual Data Cleaning with AI

The manual process of cleaning data can be tedious and prone to errors. Copying, pasting, splitting, and combining cells by hand not only consumes valuable time but also diverts focus from more strategic tasks. This is particularly true when dealing with large datasets or recurring data imports. Integrating AI for cleaning data, specifically ChatGPT, into your workflow can dramatically reduce this burden, offering quick and accurate solutions to common data dilemmas.

Whether you need to standardize customer addresses, filter sales records, or prepare HR data for reporting, ChatGPT can generate the exact Excel formulas or instructions you need. This means less time spent wrestling with spreadsheets and more time dedicated to analyzing the clean data for actionable insights. For other ways ChatGPT can streamline professional tasks, consider how to write a PRD with ChatGPT efficiently.

Prompt 1: Combine Multiple Columns into One (e.g., Full Address)

Often, data comes in a fragmented format, like an address spread across several columns (Flat No., Street, City, State, Pin Code). To create a unified record, you'll need to combine these. As observed in a data cleaning scenario, the goal might be: "I want to combine this address and I want to put it in the full address column." This is a common task that chatgpt combine columns excel can simplify.

The Prompt:

I have data in columns A, B, C, D, E, and F in Excel. I want to combine the text from these six columns into a single column G, with each part separated by a comma and a space. For example, if A1 is "123", B1 is "Main St", C1 is "Mumbai", D1 is "Maharashtra", E1 is "India", and F1 is "400001", then G1 should be "123, Main St, Mumbai, Maharashtra, India, 400001". Provide the Excel formula for cell G1.

ChatGPT's Expected Output (Example):

=TEXTJOIN(", ", TRUE, A1:F1)

This formula uses TEXTJOIN, which is excellent for combining text from multiple ranges with a specified delimiter, ignoring empty cells.

Prompt 2: Split a Single Column into Multiple Parts (e.g., Address into Flat No, Street, City)

Conversely, you might receive data where multiple pieces of information are jammed into a single cell. For analysis or database import, you'll need to separate these. For instance, you might say, "I want to split this address into three parts. I want to keep it flat number to flat number column, the middle address to middle address column, and this city and last name to pin code column." This is a classic use case for chatgpt split text to columns.

The Prompt:

I have a full address in column A, like "Flat 101, Green Apartments, Bandra, Mumbai 400050". I want to split this into three separate columns:
    Column B: "Flat No/Building Name"
    Column C: "Street/Locality"
    Column D: "City and Pin Code"

    Provide Excel formulas for cells B1, C1, and D1 that can extract these parts. Assume the parts are generally separated by commas.
    

ChatGPT's Expected Output (Example):

For B1 (Flat No/Building Name):
    =TRIM(LEFT(A1, FIND(",", A1, 1)-1))

    For C1 (Street/Locality):
    =TRIM(MID(A1, FIND(",", A1, 1)+1, FIND(",", A1, FIND(",", A1, 1)+1) - (FIND(",", A1, 1)+1)))

    For D1 (City and Pin Code):
    =TRIM(MID(A1, FIND(",", A1, FIND(",", A1, 1)+1)+1, LEN(A1)))
    

Pro-Tip: When splitting complex text, always provide a sample row in your prompt. This helps ChatGPT understand the pattern and generate more accurate formulas.

Prompt 3: Filter a List Based on a Specific Criterion (e.g., Find all 'Vegan' Guests)

Filtering data is fundamental for targeted analysis. Whether you're segmenting customers, identifying specific employees, or managing event attendees, you often need to extract a subset of your data. For example, if you have an event sheet, you might want to "get names of those people who have opted vegan." This demonstrates how chatgpt for data cleaning can help you apply specific criteria.

The Prompt:

I have an Excel sheet with guest names in Column B and their dietary preferences in Column I. I want a list of all guests who have "Vegan" as their dietary preference. Provide an Excel formula that I can use in a new column (say, Column K) to list these names. If a guest is vegan, show their name; otherwise, show nothing.

ChatGPT's Expected Output (Example):

=IF(I1="Vegan", B1, "")

To get a dynamic list without blank cells, you might follow up with a prompt asking for an advanced filtering technique or a formula using FILTER (if available in your Excel version).

Prompt 4: Extract Data Based on a Condition (e.g., Guests Arriving After 12 PM)

Beyond simple filtering, you might need to extract specific pieces of information based on numerical or temporal conditions. This is common in logistics, event management, or sales tracking. For instance, if you have arrival times for guests, you might need to identify all "Guests Arriving After 12 PM" to manage check-in staff efficiently.

The Prompt:

I have a list of guests in Column B and their arrival times in Column H (formatted as 'HH:MM AM/PM'). I need to identify all guests scheduled to arrive after 12:00 PM. Provide an Excel formula for a new column (say, Column L) that displays the guest's name if their arrival time is after 12:00 PM, otherwise it should be blank.

ChatGPT's Expected Output (Example):

=IF(H1 > TIME(12,0,0), B1, "")

This prompt leverages Excel's TIME function to compare time values, making it easy to extract data based on specific time conditions. You can adapt this for dates, numbers, or other conditional extractions, further enhancing your data cleaning in excel prompts repertoire.

Prompt 5: Calculate Conditional Sums (e.g., Total Sales for 'Furniture')

Data cleaning isn't just about reformatting; it also involves preparing data for analysis, which often includes conditional calculations. Extending on the idea that ChatGPT can assist with various data cleaning tasks, including calculations, here's how to get conditional sums. For example, you might want to find the "Total Sales for 'Furniture'" from a large sales dataset to understand category performance.

The Prompt:

I have an Excel sheet with product categories in Column O and sales figures in Column R. I need to calculate the total sales for products categorized as "Furniture". Provide the Excel formula to get this sum.

ChatGPT's Expected Output (Example):

=SUMIF(O:O, "Furniture", R:R)

The SUMIF function is perfect for this, allowing you to sum values in one range based on a criterion in another. This demonstrates ChatGPT's ability to help with analytical preparations, making your chatgpt for data cleaning efforts more comprehensive. To deepen your understanding of how AI tools can transform your workflow, consider exploring Juno's ChatGPT for Data Analysis free certificate course.