Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 2, Virtual 2

Text Cleaning Practice, and Your First Look at Verdant Foods

You finish the TechPlug Cleaning Process on your own, Steps 8 to 10. Then you open the pod project file for the first time and fix two of its four problems.

In Live 2 your tutor cleaned Staff_Name while you watched. Today nobody is watching. That is the point: the only way to know you can clean a column is to clean one and prove it worked.

Part 1: Finishing the TechPlug Cleaning Process

Step 8: Replace the Old Column

Your helper column J holds =PROPER(TRIM(I2)), filled down. A formula column depends on the original: delete column I and J turns into errors. So the clean copy goes back over the original as values, and the helper is deleted:

Step 9: Repeat for the Other Columns

The same three moves (insert a helper, =PROPER(TRIM(cell)), paste values back) on Branch, Category and Customer_Type. After Step 8 the columns are back in their places: Branch J, Category E, Customer_Type K.

Unknown passes through untouched, because it was already written properly in Step 5. Do Category the same way with =PROPER(TRIM(E2)).

Why Step 5 Comes Before Step 9

What if someone ran Step 9 on Customer_Type before filling the blanks with Unknown? A formula always returns something, and for a blank cell =PROPER(TRIM(K2)) returns empty text: a value with nothing in it. Ask Excel whether that is blank:

FALSE, even on the rows that look empty. Pasted back as values, that empty text would leave fifteen cells that look blank but are not: a filter on (Blanks) might still find them, but ISBLANK would not, and nothing would warn you. Filling Unknown first means there are no blanks left for PROPER and TRIM to turn into empty text. The order of the Cleaning Process is not decoration.

Step 10: Check Your Work

Open each filter list and compare it with this table, exactly as Step 10 says. Then check the row count one last time: Count on column A says 1,051. Your Sales Data tab now matches TechPlug_Sales_Data_Cleaned.csv, the catch-up file in the downloads. Week 3 starts from this state, so save it.

Part 2: Verdant Foods, Opened Safely

Why We Open This File Differently

TechPlug was imported with Data > From Text/CSV. Verdant Foods is opened with File > Open instead, and the reason is its Date column. It mixes two formats on purpose: most dates are written 2025-01-31, and ten are written 31/01/2025.

From Text/CSV decides one type for the whole column. On a laptop that reads dates month-first, it cannot read 31/01/2025 (there is no month 31) and turns those ten cells into errors, which load as empty cells: the evidence is gone. File > Open is less strict. It keeps whatever it cannot read as text, so the ten dates stay in the sheet, where you can see them and fix them.

Two tabs now. Raw Data is never touched again. All the work happens on Cleaned Data. If your pod already has a shared workbook from your tutor, do this in that file instead of a new one.

Fix the Category Column

Verdant sells four categories, but 30 cells spell them differently: wrong case, spaces before or after, or both. You know the fix.

Open the filter list on Category_Clean: four entries, Beverages, Cereals, Condiments and Snacks. Paste Values over column F, delete column K, and write in your notes: Category, 30 cells, wrong case and extra spaces, fixed with TRIM and PROPER.

Part 3: The Dates in the Wrong Format

Step 1: Find Out Which Kind of Laptop You Have

The same file behaves differently on different laptops, so first find out what yours did. ISTEXT is the sibling of ISNUMBER: it answers TRUE when a cell holds text.

Filter Date_Is_Text for TRUE and read the status bar.

Step 2: Turn the Text Into Real Dates With Text to Columns

Text to Columns splits text into columns, but its last screen also lets you say how to read a date. We use it on the ten text dates only:

DMY tells Excel "day, then month, then year", so 31/01/2025 becomes the real date 31 January 2025 on any laptop. Check: Date_Is_Text should now say FALSE on every row. Then sort by Transaction_ID, A to Z, to put the rows back in order, and delete the helper column.

Or: Do It With a Formula

If you prefer formulas, the same fix uses the text functions from this week. In 31/01/2025 the day is the first 2 characters, the month is 2 characters starting at position 4, and the year is the last 4. DATE(year, month, day) builds a real date from those three pieces, and IF only does it to the text cells:

Then Paste Values over column B and delete column K. Both routes give the same ten real dates.

Step 3: One Format for the Whole Column

If Your Laptop Already Read Them: the Notepad Check

On a day-first laptop all the dates are real already, so you cannot see which ten were written differently, but the Cleaning Log still needs them. Open the CSV itself in Notepad (right-click the file, Open with, Notepad), press Ctrl + F and search for /2025. Every other date uses hyphens, so each match is one of the ten.

VF-0028,2025-01-31,North Central,FCT Abuja,Verdant Corn Flakes 500g,Cereals,35,3500,Yakubu Danjuma,Retail
VF-0029,2025-01-31,South East,Enugu,Verdant Tomato Paste 400g,Condiments,12,1400,Funmilayo Adeyemi,Retail
VF-0030,31/01/2025,South West,Lagos,Verdant Tomato Paste 400g,Condiments,184,1400,Funmilayo Adeyemi,Wholesale

Note the Transaction_ID of each match for your log. There are exactly 10.

Checkpoint

Next: Pod Task Week 2. The full cleaning brief for Verdant Foods: the 15 duplicates, the 20 blank quantities, the Quantity_Flag column, and the Cleaning Log.