Why clean text is the first skill of every records clerk
If you work in data entry, records, administration or a back office, you already know the feeling: a list arrives from a web form, a colleague or another system, and it is a mess. Names in capitals, cities in lower case, a postcode with a space at the end. Each fault is tiny, and each one breaks something later: a lookup that returns #N/A, a duplicate that is not caught, a mail merge that prints "Dear MARGARET".
This free lesson is for data entry clerks, records assistants, administrators, office juniors and anyone who has to keep a customer or supplier list tidy. You do not need any Excel experience beyond typing in a cell. In the lab above you start as a records clerk at Brookfield Medical Supplies, a fictional company, and clean four new customer records that were typed in a hurry. The names, cities and postcodes are made up for learning.
By the end of the lesson you will know three functions, TRIM, PROPER and UPPER, how to combine them in one formula, and how to turn the results into clean values that can replace the raw data. These are the most used text-cleaning functions in Excel and you will use them in almost every later lesson.
The core idea: Excel compares characters, not meaning
A person reading "leeds", "Leeds" and " Leeds" sees the same city. Excel does not see a city at all. It sees a string of characters, and a space is a character like any other. For many operations this matters a lot:
- Lookups such as XLOOKUP and VLOOKUP treat "Leeds " (with a trailing space) and "Leeds" as different values, so the lookup fails with #N/A.
- Duplicate checks with COUNTIF or Remove Duplicates do not see "york " and "York" as the same record.
- Sorting and filtering puts " hull" (with a leading space) at the top of the list instead of under H.
- PivotTables show two separate rows for the same city written in two ways.
Capital letters are more forgiving: most Excel lookups and COUNTIF ignore the difference between upper and lower case. But readers do not. A letter addressed to "DAVID chen" or a label reading "priya shah" looks careless, and many organisations have a house style: names in proper case, postcodes and codes in capitals, e-mail addresses in lower case.
Three functions cover most of this work:
- TRIM removes spaces at the start and at the end of the text, and squeezes any run of spaces inside the text down to a single space.
=TRIM(" margaret OKAFOR")returns "margaret OKAFOR". - PROPER writes the first letter of every word in capitals and all other letters in lower case.
=PROPER("margaret OKAFOR")returns "Margaret Okafor". - UPPER writes every letter in capitals, which is the standard for postcodes, country codes and product codes. Its partner LOWER does the opposite and is the usual choice for e-mail addresses.
Because each function takes text and returns text, you can nest them: put one inside the other. Excel always works from the inside out, so =PROPER(TRIM(A2)) first trims the text in A2 and then fixes the capitals of the trimmed result.
Worked example: four records from Brookfield Medical Supplies
Here are the four records from the lab. In the raw columns every space is shown as a middle dot (·) so you can see what the screen hides. The clean columns show what your formulas will return:
| Row | Name (raw) | City (raw) | Postcode (raw) | Name | City | Postcode |
|---|---|---|---|---|---|---|
| 2 | ··margaret··OKAFOR | ·leeds | ls6·2qa· | Margaret Okafor | Leeds | LS6 2QA |
| 3 | DAVID·chen· | york·· | ·yo1·7hh | David Chen | York | YO1 7HH |
| 4 | priya··shah | BRADFORD | bd1·4as | Priya Shah | Bradford | BD1 4AS |
| 5 | ·tom·o·brien | ·hull | hu2··8bb· | Tom O Brien | Hull | HU2 8BB |
Look closely at a few cells. "DAVID chen " has a trailing space you would never notice on screen. "york··" has two spaces at the end. "BRADFORD" has no extra spaces at all, only capitals, so TRIM changes nothing and PROPER does all the work. "hu2··8bb·" has a double space inside the postcode and one at the end. TRIM leaves a single space in the middle, which is exactly where a UK postcode needs it, and UPPER turns it into "HU2 8BB".
Here are the exact formulas, plus two check formulas that prove the cleaning worked:
| Cell | Formula | Result |
|---|---|---|
| D2 (copy to D5) | =PROPER(TRIM(A2)) | Margaret Okafor |
| E2 (copy to E5) | =PROPER(TRIM(B2)) | Leeds |
| F2 (copy to F5) | =UPPER(TRIM(C2)) | LS6 2QA |
| G2 (check) | =LEN(A2)-LEN(TRIM(A2)) | 3 extra spaces |
| H2 (check) | =EXACT(D2,PROPER(D2)) | TRUE when the capitals are already clean |
| (e-mail) | =LOWER(TRIM(A2)) | for an e-mail column: all lower case |
| (web data) | =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) | also removes non-breaking spaces |
The check formula with LEN is worth remembering. LEN counts every character, spaces included, so =LEN(A2)-LEN(TRIM(A2)) tells you how many extra spaces a cell contains. Copied down a whole column and added up, it shows at a glance how much hidden mess a list has. In this small lab, ten of the twelve raw cells contained extra spaces:
Step by step: cleaning a column in Excel
Follow these steps in the lab above, or in your own workbook. The menu paths are for Excel for Microsoft 365 on Windows. Mac menus have the same names.
- Keep the raw data. Never clean in place. Leave the raw columns (A to C in the lab) as they are and clean into new columns next to them (D to F). If something goes wrong, the original is still there.
- Add clear headers. Name the new columns "Name", "City" and "Postcode" so nobody confuses raw and clean data.
- Type the first formula. Click D2 and type
=PROPER(TRIM(A2)). Excel shows a tooltip with the function arguments as you type. You can also insert functions from Formulas → Function Library → Text. - Copy it down. Select D2:D5 and press Ctrl+D, or double-click the small square at the bottom-right corner of D2 (the fill handle) to fill down as far as the data goes.
- Clean the cities the same way in E2:E5 with
=PROPER(TRIM(B2)). - Clean the postcodes in F2:F5 with
=UPPER(TRIM(C2)). - Check the result. In a spare column, type
=LEN(D2)next to=LEN(A2), or use=LEN(A2)-LEN(TRIM(A2)). Scan the clean names for PROPER exceptions such as "Mc" and "van". - Turn formulas into values. Select D2:F5, copy with Ctrl+C, then Home → Paste → Paste Values (or Ctrl+Alt+V, then V, then Enter). The cells now hold text, not formulas.
- Replace the raw data, if your process allows it. Once the values are pasted and checked, you can paste them over the raw columns and delete the helper columns, or keep both and hide the raw ones (right-click the column header → Hide).
Formulas, Find and Replace or Flash Fill: which one when?
Excel gives you more than one way to clean text, and each has its place. Formulas such as =PROPER(TRIM(A2)) are the safest choice when the data will arrive again next week: you keep the formula column, paste the new raw data next to it, and the clean values appear by themselves. They are also transparent, because anyone can click a cell and see exactly what was done.
Find and Replace (Ctrl+H) works in place, without a helper column. Replacing two spaces with one removes double spaces inside the text, but you have to repeat it until Excel reports no more replacements, and it cannot touch leading or trailing spaces in a controlled way or fix capitals. Use it for quick, one-off fixes on a copy of the data.
Flash Fill (Ctrl+E) guesses a pattern from your examples. It is impressive on irregular data, but it can guess wrong on a few rows without telling you, and it never updates. Whatever method you use, finish with a check: a LEN comparison or a quick filter on the clean column shows any value that still looks odd.

Common mistakes and how to spot them
Text cleaning looks easy, which is exactly why small errors slip through. Watch for these:
- PROPER without TRIM.
=PROPER(A2)fixes the capitals but keeps the spaces. Spot it: the result looks right on screen, but=LEN(D2)is longer than expected, or a lookup on the clean column still fails. - UPPER without TRIM on codes. " YO1 7HH" with a leading space will not match "YO1 7HH" in a lookup. Always trim codes as well.
- Non-breaking spaces. Text copied from web pages and some systems contains CHAR(160), a space that TRIM does not remove. Spot it: LEN stays the same after TRIM although you can see a space. Fix it with
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")). - Cleaning over the raw data. Typing a formula that refers to its own cell creates a circular reference. Always clean into a new column.
- Forgetting to paste as values. If you delete the raw columns while the clean ones still hold formulas, every clean cell turns into #REF!. Paste values first, then delete.
- Using PROPER on codes or e-mails. "Ls6 2qa" and "Jane.Doe@Example.Com" are both wrong. Codes take UPPER, e-mail addresses LOWER.
- Trusting PROPER blindly on names. Check names such as McDonald, MacLeod, van der Berg or double-barrelled names and correct them by hand.
Quick reference: functions and shortcuts
| Function or shortcut | What it does |
|---|---|
TRIM(text) | Removes leading and trailing spaces and reduces runs of spaces inside the text to one. |
PROPER(text) | Capital first letter for each word, the rest lower case. |
UPPER(text) / LOWER(text) | All capitals (codes, postcodes) / all lower case (e-mail addresses). |
LEN(text) | Counts characters, including spaces. Used to prove that spaces were removed. |
CLEAN(text) | Removes non-printing characters such as line breaks copied from other systems. |
SUBSTITUTE(text,CHAR(160)," ") | Turns non-breaking spaces from web pages into normal spaces so TRIM can remove them. |
EXACT(a,b) | Case-sensitive comparison: TRUE only if both texts match exactly. |
| Ctrl+E | Flash Fill: fills a column from one or two typed examples (static, does not update). |
| Ctrl+C, then Ctrl+Alt+V, V, Enter | Paste Special → Values on Windows: replaces formulas with their results. |
| Ctrl+H | Find and Replace, for example to replace double spaces in place. |
Real-world uses
The same three functions solve problems that records clerks face every week:
- Before a mail merge. Clean names and addresses so letters and labels read "Dear Margaret Okafor", not "Dear margaret OKAFOR".
- Before importing into a CRM or ERP. Many systems reject or duplicate records with trailing spaces. Trim every text field before the upload.
- Before matching two lists. Trim both the lookup value and the lookup column, then XLOOKUP finds the matches it was missing.
- Standardising codes. Product codes, postcodes and country codes in capitals make sorting, filtering and PivotTables behave.
- Cleaning e-mail lists.
=LOWER(TRIM(A2))on an e-mail column removes the spaces and capitals that cause duplicate contacts.
What you will build in the rest of this course
This first lesson is free. Lessons 2 to 12 are part of the full course, unlocked with a one-time payment of $9.90. Every lesson is a hands-on lab at Brookfield Medical Supplies, checked cell by cell by the Coach:
Splitting Order References and E-mails. You split order references into region, order number and batch letter with LEFT, MID and RIGHT, and pull the domain out of each e-mail address with TEXTAFTER.
Joining Fields. You join separate fields into full names, one-line address labels and short record keys, the reverse of splitting.
Converting Text Numbers and Text Dates. Web-form data often stores dates and quantities as text. You turn them into real numbers and dates with VALUE and DATE so they can be added up and sorted.
Finding Duplicates. Supplier invoices keyed twice get paid twice. You flag duplicates with COUNTIF and make them stand out with a conditional formatting rule.
Checking Required Fields. You find missing contacts, phone numbers, e-mails and postcodes with COUNTBLANK, ISBLANK and IF before records go into the system.
Validating Entries. You check keyed orders against entry rules: quantities in a range, a maximum discount and a fixed-length customer number, using AND and LEN.
Matching Two Lists. You match the order list against the warehouse delivery list with XLOOKUP and show "Not found" for missing orders instead of #N/A.
Standardising Categories with a Lookup Table. Staff typed one department ten different ways. You map every spelling to one standard name and code.
Record Counts and Completeness Rate per Field. You report how complete the customer file is, field by field, and find the field that needs the most work.
Daily Entry Log. You keep your own log of hours, records entered, records per hour and error rate for a week.
Data Quality Dashboard. You build the weekly data quality page for the records team: clean rate against a 95% target and a chart of issue types.
Summary
Excel compares characters, not meaning, so extra spaces and inconsistent capitals break lookups, duplicate checks and sorting. TRIM removes leading, trailing and double spaces. PROPER fixes the capitals of names and places, UPPER standardises codes and LOWER e-mail addresses. Nest them as =PROPER(TRIM(A2)), always clean into a new column, check with LEN, and paste the results as values when you are done.
Now scroll up and try the lab: clean the four Brookfield records in columns D to F. The Coach checks every cell and tells you exactly what is still wrong. It takes about fifteen minutes and it is free.
Frequently asked questions
How do I remove extra spaces in Excel?
Use =TRIM(A2) in a new column. TRIM removes spaces at the start and end of the text and reduces double spaces inside it to a single space. Then copy the results and paste them as values.
Why does TRIM not remove all spaces?
Text from web pages often contains non-breaking spaces (CHAR(160)), which TRIM ignores. Replace them first with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
How do I capitalise the first letter of each word in Excel?
Use =PROPER(A2). It writes the first letter of every word in capitals and the rest in lower case. Check names such as McDonald afterwards, because PROPER returns Mcdonald.
Can I combine TRIM and PROPER in one formula?
Yes. Nest them as =PROPER(TRIM(A2)). Excel runs the inner function first, so the spaces are removed before the capitals are fixed.
How do I convert postcodes or codes to capital letters?
Use =UPPER(TRIM(A2)). UPPER turns every letter into a capital and TRIM removes stray spaces, so the codes match in lookups and sort correctly.
How do I replace formulas with the cleaned text?
Select the clean cells, copy them with Ctrl+C, then use Home, Paste, Paste Values. The cells now contain text instead of formulas and you can delete the raw columns safely.
Keep going: Excel for Data Entry & Records Clerks: Clean, Check and Match Data
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Splitting Order References and E-mails: LEFT, MID, RIGHT and TEXTAFTER
- Joining Fields: Full Names, Address Labels and Record Keys
- Converting Text Numbers and Text Dates: VALUE and DATE
- Finding Duplicates: COUNTIF Flags and a Highlight Rule
- Checking Required Fields: COUNTBLANK, ISBLANK and IF
- Validating Entries: Ranges with AND, Formats with LEN
- Matching Two Lists: XLOOKUP, ISNA and "Not found"
- Standardising Categories with a Lookup Table
- Record Counts and Completeness Rate per Field
- Daily Entry Log: Hours, Records per Hour and Error Rate
- Data Quality Dashboard: Clean Rate, Target Check and Issue Chart
- Phone Numbers and Account Codes: SUBSTITUTE, LEN Checks and Leading Zeros
- Double-Keying Check: Comparing Two Entries Field by Field with EXACT
- Batch Control Totals: Record Counts, Amount Totals and Hash Totals
- Monthly Entry Summary: SUMIFS by Clerk and Document Type, Totals and a Stacked Chart
- Records Retention: Lookup of Keep Periods, EDATE and Due-for-Review Flags
- Turnaround Times: NETWORKDAYS, a Two-Day Target and the Open Backlog
- Check Digits: Catching Typing Errors in Account Numbers with MID and MOD
- Final Project – Monthly Records Report: Speed, Error Rate, On-Time Rate and a Status per Team