Fix Messy CSV Data After Importing to Excel

# Excel Cleanup Plan: Sales CSV (8,000 Records)

## Step 1: Back up your file
Save a copy as `SalesData_RAW.xlsx` before editing. Work on a duplicate sheet so you can revert if needed.

## Step 2: Split column A by semicolons (Text to Columns)
1. Select column A → **Data → Text to Columns**
2. Choose **Delimited** → Next
3. Check **Semicolon** (uncheck others) → Next
4. For the date column, click it in the preview and set **Column data format → Date: YMD** (or leave as Text if dates are mixed — see Step 5)
5. Click **Finish**

*Alternative (recommended for repeat imports):* **Data → Get Data → From Text/CSV**, set delimiter to Semicolon in Power Query, then Close & Load.

## Step 3: Strip leading/trailing spaces and hidden characters
In a helper column next to each text field (e.g., names):
```
=TRIM(CLEAN(B2))
```
Drag down, then **Copy → Paste Special → Values** over the original column and delete the helper.
*Power Query equivalent:* Right-click column → **Transform → Trim** and **Transform → Clean**.

## Step 4: Standardize date formats
Because dates are mixed (`3/5/24` and `2024-03-05`), Excel likely stores some as text. In a helper column:
```
=IFERROR(DATEVALUE(C2), C2)
```
Then:
1. Select the helper column → format cells as **Date (YYYY-MM-DD)**
2. Paste values back over the original column
3. Spot-check ambiguous dates like `3/5/24` — confirm whether your source is US (M/D/Y) or EU (D/M/Y) before trusting DATEVALUE.

*Power Query method (more reliable):* Select column → **Transform → Data Type → Using Locale → Date → English (United States)** or matching locale.

## Step 5: Remove duplicate order IDs
1. Select the full data range (include headers)
2. **Data → Remove Duplicates**
3. Uncheck all columns, then check **only Order ID**
4. Click OK — Excel reports how many duplicates were removed

*If you need to review before deleting:* Apply **Conditional Formatting → Highlight Cell Rules → Duplicate Values** on the Order ID column first.

## Step 6: Convert to an Excel Table
Select data → **Ctrl + T** → confirm headers. Tables auto-expand, enable filters, and make future formulas cleaner.

## Step 7: Final validation checks
- Sort/filter each column to spot blanks or outliers
- Verify row count matches expectations (~8,000 minus duplicates)
- Check totals with `SUM` or a quick PivotTable to confirm numeric columns parsed correctly

---

### 💡 Tip to prevent this next time
**Use Power Query instead of opening CSVs directly.** Go to **Data → Get Data → From Text/CSV**, and Excel will let you set the delimiter, trim whitespace, fix date locales, and remove duplicates *before* loading. Save the query — next month's import becomes a one-click **Refresh**.

# Excel Data-Cleaning Plan for Your Sales Records

## Step-by-Step Cleanup Guide

### **Step 1: Separate Semicolon-Delimited Data into Columns**
1. Select column A (click the column header)
2. Go to **Data > Text to Columns**
3. Choose **Delimited** → **Next**
4. Check **Semicolon** as the delimiter → **Next**
5. Verify column formats (set date columns to "Text" for now) → **Finish**

*This splits your 8,000 records across separate columns.*

---

### **Step 2: Trim Leading/Trailing Spaces from Names**
1. Insert a helper column next to your names column (e.g., column B if names are in A)
2. Enter the formula: `=TRIM(A2)`
3. Press **Enter**, then double-click the fill handle (small square at cell bottom-right) to auto-fill down all 8,000 rows
4. Copy the trimmed column → Paste Special > **Values only** back into the original column
5. Delete the helper column

*Repeat for any other text columns with spacing issues.*

---

### **Step 3: Standardize Date Formats**
1. Select your date column
2. Insert a helper column and use this formula:
   ```
   =TEXT(DATEVALUE(A2),"YYYY-MM-DD")
   ```
   *(Adjust `A2` to your date column reference)*
3. Auto-fill down with the fill handle
4. Copy → Paste Special > **Values only** back to the original column
5. Delete the helper column
6. Right-click the date column → **Format Cells > Date** → choose a single consistent format (e.g., YYYY-MM-DD)

*This converts both 3/5/24 and 2024-03-05 formats to a standard format.*

---

### **Step 4: Remove Duplicate Order IDs**
1. Select your entire data range (or click any cell in the data)
2. Go to **Data > Remove Duplicates**
3. Check only the **Order ID** column
4. Click **OK**

*Excel will highlight and remove rows where Order IDs are identical. Review the deletion summary.*

**Alternative (if you need to keep one copy and flag duplicates):**
- Insert a helper column with formula: `=COUNTIF($A$2:$A2,A2)>1`
- Filter for `TRUE` values to review before deleting manually

---

### **Step 5: Clean Any Remaining Special Characters (Optional)**
If you notice stray quotes, line breaks, or odd characters:
1. Select affected columns
2. Go to **Home > Find & Replace** (Ctrl+H)
3. Find: `"` (or problematic character) → Replace with: (leave blank) → **Replace All**
4. Repeat for other characters (e.g., line breaks: find `^j`, replace with space)

---

### **Step 6: Verify Data Quality**
1. Scroll through a sample of rows to spot-check formatting
2. Use **Data > AutoFilter** to check for blanks or obvious errors:
   - Click column headers → filter to see unique values
3. Check Order ID column for remaining duplicates using **Remove Duplicates** again if needed

---

### **Step 7: Save Your Cleaned File**
1. Go to **File > Save As**
2. Choose **Excel Workbook (.xlsx)** format (not CSV) to preserve formatting
3. Name it something like `Sales_Records_Cleaned.xlsx`

---

## **Prevention Tip for Future Imports**
When importing CSVs in the future:
- **Use Power Query** (Data > Get Data > From File > From CSV) instead of direct CSV import—it auto-detects delimiters and handles formatting better
- Or: Set column widths and formats *before* importing to guide the import wizard
- Always import to a temporary sheet, clean, then move to your main workbook

---

## **Quick Summary**
| Issue | Solution | Steps |
|-------|----------|-------|
| Semicolon-separated data | Text to Columns | 1 |
| Spaces in names | TRIM formula | 2 |
| Mixed date formats | TEXT/DATEVALUE + Format Cells | 3 |
| Duplicate Order IDs | Remove Duplicates | 4 |
| Special characters | Find & Replace | 5 |

**Estimated time:** 15–20 minutes for 8,000 rows using these built-in tools.

Of course. Here is a step-by-step guide to clean your sales data using Power Query, which is the most efficient and repeatable way to handle all the issues you've described.

### **Your 6-Step Data Cleanup Plan**

This plan uses the Power Query Editor, which is built into Excel. It's the best tool because it records your steps, allowing you to refresh the data with one click if the source file is ever updated.

---

#### **Step 1: Load the Messy Data into Power Query**

First, we need to load your data into the Power Query Editor correctly. This is where you can tell Excel that your data is separated by semicolons.

*   **Action:**
    1.  Select the single cell containing your messy data (e.g., A1).
    2.  Go to the **Data** tab on the ribbon.
    3.  In the "Get & Transform Data" group, click **From Table/Range**.
    4.  Excel will open the Power Query Editor with your single column of data.

#### **Step 2: Split the Single Column into Multiple Columns**

This will fix the core problem of all your data being stuck in Column A.

*   **Action:**
    1.  In the Power Query Editor, select the column header.
    2.  Go to the **Home** tab.
    3.  Click **Split Column** > **By Delimiter**.
    4.  A new window will pop up. Power Query is usually smart enough to detect the semicolon, but if not,

1. **Prepare data as a table**: Select all data in column A (Ctrl+A if it's the only data). Go to **Insert > Table** (or Ctrl+T). Ensure "My table has headers" is checked if row 1 has headers; click OK. This enables Power Query.

2. **Load into Power Query**: With the table selected, go to **Data > From Table/Range**. Power Query Editor opens with your data in one column.

3. **Split columns by semicolon**: In Power Query, select column A. Go to **Home > Split Column > By Delimiter**. Choose **Semicolon** as delimiter, select **Split at Each Occurrence** and **Columns**, then OK. This creates separate columns (e.g., OrderID, Name, Date, etc.).

4. **Trim spaces from name column**: Click the header of the name column (e.g., Column2). Go to **Transform > Format > Trim**. Repeat for any other text columns with spaces. (Use **Transform > Replace Values** with " " (space) to nothing if extra internal spaces.)

5. **Standardize date column**: Click the date column header (e.g., Column3). Go to **Home > Data Type > Date** (or **Using Locale** > select English(US) for MM/DD/YY or appropriate for YYYY-MM-DD). Power Query auto-parses most formats; preview and adjust locale if needed.

6. **Remove duplicate order IDs**: Click the Order ID column header (e.g., Column1). Go to **Home > Remove Rows > Remove Duplicates**. This keeps first unique instance per Order ID across all columns.

7. **Verify and finalize**: Scroll/review rows (use **Home > Keep Rows > Keep Top Rows** for sample). Rename columns via double-click headers. Go to **Home > Close & Load** to output cleaned table to a new sheet.

8. **Optional: Clean non-printable chars**: If needed post-load, select affected columns, go to **Data > Text to Columns > Delimited > Next > uncheck all > Finish** (triggers CLEAN equivalent), or use formula `=CLEAN(TRIM(A1))` in new column and copy down.

**Future import tip**: When opening CSV, use **Data > From Text/CSV**, select semicolon delimiter upfront, and load directly to Power Query for auto-cleaning—avoids single-column mess.