Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 2, Virtual 1
Blank Handling Practice, and LEFT, RIGHT, MID and LEN
First you finish the blank audit on every column of Sales Data. Then you learn four functions that take text apart, and discover why text has to be cleaned before it can be trusted.
By the end of this class you can
- Count the blanks in every column and log a decision for each
- Use COUNTBLANK to check a filter count with a formula
- Pull characters from the start, end or middle of text with LEFT, RIGHT and MID
- Count the characters in a cell with LEN, and use it to expose hidden spaces
In Live 1 you followed the Cleaning Process tab for Quantity and Customer_Type because it told you where the blanks were. Today you check every column yourself, so that "no other blanks" is something you proved, not something you were told. Then we start on the other half of this week's work, which is text.
What you do in this class
- Filter every Sales Data column for (Blanks), count them, and fill in the Blank Log in Part 1.
- Check your counts with COUNTBLANK.
- Watch the short demo clip on LEFT, RIGHT, MID and LEN, then follow Part 2 on your own sheet.
- Do the Try This in each section and the four exercises.
Hand in: Your Blank Log (column, blanks, decision, why) and a sheet with the four text functions working.
Part 1: The Blank Log
Work on Sales Data after Step 3 (duplicates removed). If you have already done Step 5, Customer_Type has no blanks left, because they now say Unknown: use the Raw Data tab with duplicates removed for this part, or simply write the count from Live 1.
For each of the twelve columns: open its filter arrow, untick (Select All), tick (Blanks) if it is there, read the status bar, and clear the filter. If (Blanks) is not in the list at all, that column has no blanks. Write the result in a small table on a new sheet called Notes.
Checking the Counts With COUNTBLANK
A filter count is only as good as your clicking. A formula gives you a second, independent count. COUNTBLANK(range) counts the empty cells in a range. Give it each column's data, from row 2 to row 1,051, and it answers in one go.
Two columns have blanks: Quantity (25) and Customer_Type (15). Every other column is complete. If your filter counts agree, your audit is right.
Type F2:F1051, not F2:F1050 and not F:F. F2:F1050 misses the last sale. F:F counts the million empty cells below the data as well, and the answer is meaningless.
Your Decisions
| Column | Blank cells | Decision | Why |
|---|---|---|---|
| Quantity | 25 | Leave blank (Step 4) | Real sales whose quantity nobody knows. Week 3 shows how a formula handles them |
| Customer_Type | 15 | Fill with Unknown (Step 5) | A grouping label; Unknown keeps the sale in every summary |
| The other ten columns | 0 | Nothing to do | Complete |
Click an empty cell in the Quantity column and press the Space bar, then Enter. Run COUNTBLANK on F2:F1051 again. Did the count drop by one? Press Ctrl + Z to undo.
Check your answer
It drops by one: the cell holds a space now, so it is not blank, even though it looks blank.
Picture a register with two kinds of gaps: the ones nobody can fill (leave them, honestly empty) and the ones you can label (write "not recorded" so the student is still counted). The Blank Log is the sticky note that says which is which.
Part 2: Taking Text Apart
Definition
Text functions treat a cell's text as a row of characters, counted from the left starting at 1. Letters, digits, spaces and punctuation all count as one character each. With four functions you can count those characters and pull out any piece of them.
Real-Life Scenario
A Nigerian phone number. The first four digits (0803, 0805, 0816) tell you the network, the last few digits are what people read out to confirm it, and the total length tells you at once whether a digit is missing. You already take phone numbers apart in your head. These functions do it for a thousand cells at once.
The Four Functions
| Function | Question it answers | Pattern |
|---|---|---|
| LEFT | What are the first n characters? | LEFT(text, how many) |
| RIGHT | What are the last n characters? | RIGHT(text, how many) |
| MID | What are n characters starting at position p? | MID(text, start position, how many) |
| LEN | How many characters are there in total? | LEN(text) |
Each one goes in a new column to the right of the data, in M. Type it in row 2, press Enter, and double-click the fill handle to copy it down to row 1,051. Delete it before the next one.
Function 1: LEFT
Every Transaction_ID starts with the same two letters, TP. LEFT takes characters from the start of the text.
Every row says TP. Filter the Prefix column and you will see one value only, which is a quick way to prove no ID was typed wrongly.
Function 2: RIGHT
Look closely: 0892 keeps its leading zero and sits on the left of its cell. Text functions always return text, even when the result is made of digits. That is usually what you want for an ID.
Function 3: MID
MID reaches into the middle. Product_IDs look like PRD-015: the number starts at position 5 and is 3 characters long.
MID matters when the piece you want has text on both sides of it. Its limit is that you must know the start position. For Product_IDs that is always 5. For product names, where the brand might be 2 letters or 8, a fixed position does not work, and that problem is solved in Live 2 with FIND.
Function 4: LEN
LEN counts characters, including every space. That makes it a detector for the thing your eyes cannot see: spaces at the start and end of a cell. Here it is on the Branch column, on two clean rows and some of the messy ones.
"Kano" has 4 characters and "Lagos Island" 12. Any row with a bigger number than its name should have is carrying spaces: the red dots in the grid show where. To a person they look the same; to Excel they are different values, which is why a filter or a total misses them. LEN cannot fix this, but it proves it. The fix is TRIM, in Live 2 (Step 6 of the Cleaning Process).
Using LEFT on dirty text. =LEFT(J2,3) on " Warri " returns three spaces, not "War". Text functions do exactly what you ask, character by character, spaces included. Clean first, then take apart.
Overall Example: The Longest Product Name
Sort Name_Length from largest to smallest and the top row is Logitech Wireless Mouse, at 23 characters. Why would anyone care? Because a receipt printer, a text message or a chart label often has a limit, and LEN tells you which names will be cut off before it happens.
Use LEN on Staff_Name and sort it from largest to smallest. The values at the top are the ones carrying extra spaces. Whose name is the longest once the spaces are ignored?
Check your answer
=LEN(I2) filled down, then sorted, puts the padded names at the top. Ignoring spaces, the longest real name is Yusuf Abdullahi, at 15 characters.
Practical Exercises
Exercise 1: Very Easy
Write =LEN(A2) next to the first sale. What number do you get, and why?
Exercise 2: Simple Application
Use MID to pull the number out of every Staff_ID (EMP-004 becomes 004).
Exercise 3: Combination
Use COUNTBLANK on Customer_Type, then filter it for (Blanks). Do the two counts match?
Exercise 4: A Small Real-World Problem
TechPlug wants a three-letter code for each branch for its delivery labels. Try =UPPER(LEFT(TRIM(J2),3)) and look at the two Lagos branches. Explain in one sentence why cutting names is a bad way to make codes.
Answers to the exercises
Exercise 1: 7, two letters, a hyphen and four digits. Exercise 2: =MID(H2,5,3). Exercise 4: Lagos Island and Lagos Mainland both become LAG, so two branches share one code. Codes should be designed and kept in a table, like the Staff_IDs in the Staff Directory, never cut from names.
Checkpoint
Q1. What does =LEN("TP-0892") return?
7. Two letters, a hyphen and four digits.
Q2. What does =MID("PRD-015",5,3) return, and is it a number?
015. It is text, because text functions always return text.
Q3. A Branch cell shows Warri but LEN says 11. What is going on?
There are 6 extra characters that cannot be seen: spaces before or after the name.
Q4. Why does COUNTBLANK on F2:F1050 give the wrong answer?
It stops one row early and misses the last sale, in row 1,051.
Tick Before You Move On
- ☐ I filtered every column for blanks and logged the count, the decision and the reason
- ☐ My COUNTBLANK counts agree with my filter counts
- ☐ I can use LEFT, RIGHT and MID to pull a piece of text
- ☐ I know text functions return text, even when the result is digits
- ☐ I used LEN to expose hidden spaces in Branch
Quick Recap
Every column gets a blank count and a written decision; COUNTBLANK checks the filter. LEFT, RIGHT and MID take characters from the start, the end or the middle. LEN counts every character, spaces included, which makes it a hidden-space detector. Text functions work character by character, so dirty text gives dirty results. Clean first.
Next, Week 2, Live 2: Steps 6 and 7 of the Cleaning Process. TRIM, PROPER, UPPER, LOWER and CLEAN fix the text; FIND and SUBSTITUTE find and replace pieces of it.