Tabellab

Fixing Text Case and Spaces: TRIM, PROPER and UPPER

Lesson 1 of 20Lab

All lessons
  1. Fixing Text Case and Spaces: TRIM, PROPER and UPPERLabFree
  2. Splitting Order References and E-mails: LEFT, MID, RIGHT and TEXTAFTERLab Locked
  3. Joining Fields: Full Names, Address Labels and Record KeysLab Locked
  4. Converting Text Numbers and Text Dates: VALUE and DATELab Locked
  5. Finding Duplicates: COUNTIF Flags and a Highlight RuleLab Locked
  6. Checking Required Fields: COUNTBLANK, ISBLANK and IFLab Locked
  7. Validating Entries: Ranges with AND, Formats with LENLab Locked
  8. Matching Two Lists: XLOOKUP, ISNA and "Not found"Lab Locked
  9. Standardising Categories with a Lookup TableLab Locked
  10. Record Counts and Completeness Rate per FieldLab Locked
  11. Daily Entry Log: Hours, Records per Hour and Error RateLab Locked
  12. Data Quality Dashboard: Clean Rate, Target Check and Issue ChartLab Locked
  13. Phone Numbers and Account Codes: SUBSTITUTE, LEN Checks and Leading ZerosLab Locked
  14. Double-Keying Check: Comparing Two Entries Field by Field with EXACTLab Locked
  15. Batch Control Totals: Record Counts, Amount Totals and Hash TotalsLab Locked
  16. Monthly Entry Summary: SUMIFS by Clerk and Document Type, Totals and a Stacked ChartLab Locked
  17. Records Retention: Lookup of Keep Periods, EDATE and Due-for-Review FlagsLab Locked
  18. Turnaround Times: NETWORKDAYS, a Two-Day Target and the Open BacklogLab Locked
  19. Check Digits: Catching Typing Errors in Account Numbers with MID and MODLab Locked
  20. Final Project – Monthly Records Report: Speed, Error Rate, On-Time Rate and a Status per TeamLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • Formulas → Text shows TRIM, PROPER, UPPER and LOWER.
  • Home → Paste → Paste Values replaces the formulas with the cleaned text.
  • Flash Fill (Ctrl+E) can also clean a column from one typed example, but it does not update when the raw data changes.
Loading the spreadsheet…

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.

One raw name, two functions, one clean valueRaw (A2)··margaret··OKAFORLEN = 18After TRIMmargaret·OKAFORLEN = 15After PROPERMargaret·OkaforLEN = 15TRIMPROPERIn one cell:=PROPER(TRIM(A2))· marks a space. TRIM runs first (inside), PROPER second (outside).
How the nested formula works on the first record of the lab: TRIM removes the two leading spaces and squeezes the double space, PROPER then fixes the capitals. The text gets three characters shorter.

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:

RowName (raw)City (raw)Postcode (raw)NameCityPostcode
2··margaret··OKAFOR·leedsls6·2qa·Margaret OkaforLeedsLS6 2QA
3DAVID·chen·york···yo1·7hhDavid ChenYorkYO1 7HH
4priya··shahBRADFORDbd1·4asPriya ShahBradfordBD1 4AS
5·tom·o·brien·hullhu2··8bb·Tom O BrienHullHU2 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:

CellFormulaResult
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:

Extra spaces removed by TRIM, per raw cell01234Spaces removed = LEN(raw) − LEN(TRIM(raw))A2 name3A3 name1A4 name1A5 name1B2 city1B3 city2B4 city0B5 city1C2 postcode1C3 postcode1C4 postcode0C5 postcode2
Every raw cell of the lab with the number of extra spaces TRIM removes. Only two of the twelve cells (B4 and C4) were already free of extra spaces, and you cannot tell which ones by looking at the screen.

PROPER is simple and sometimes too simple. It capitalises the first letter after every non-letter character, so "tom o brien" becomes "Tom O Brien" and "o'brien" becomes "O'Brien", which is right. But "mcdonald" becomes "Mcdonald", and "van der berg" becomes "Van Der Berg". After using PROPER on names, scan the result for these cases and fix them by hand, or keep a short list of exceptions.

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.

  1. 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.
  2. Add clear headers. Name the new columns "Name", "City" and "Postcode" so nobody confuses raw and clean data.
  3. 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.
  4. 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.
  5. Clean the cities the same way in E2:E5 with =PROPER(TRIM(B2)).
  6. Clean the postcodes in F2:F5 with =UPPER(TRIM(C2)).
  7. 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".
  8. 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.
  9. 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).
Flash Fill (Ctrl+E, or Data → Flash Fill) can clean a column from one typed example: type "Margaret Okafor" in D2, move to D3 and press Ctrl+E. It is fast for one-off jobs, but the result is static. If the raw data changes, Flash Fill does not update. Formulas do.

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.

Excel for data entry clerks course: cleaning names, cities and postcodes with TRIM, PROPER and UPPER
The course follows a records clerk at the fictional Brookfield Medical Supplies through twelve real data entry and data quality tasks.

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 shortcutWhat 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+EFlash Fill: fills a column from one or two typed examples (static, does not update).
Ctrl+C, then Ctrl+Alt+V, V, EnterPaste Special → Values on Windows: replaces formulas with their results.
Ctrl+HFind 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.

  1. Splitting Order References and E-mails: LEFT, MID, RIGHT and TEXTAFTER
  2. Joining Fields: Full Names, Address Labels and Record Keys
  3. Converting Text Numbers and Text Dates: VALUE and DATE
  4. Finding Duplicates: COUNTIF Flags and a Highlight Rule
  5. Checking Required Fields: COUNTBLANK, ISBLANK and IF
  6. Validating Entries: Ranges with AND, Formats with LEN
  7. Matching Two Lists: XLOOKUP, ISNA and "Not found"
  8. Standardising Categories with a Lookup Table
  9. Record Counts and Completeness Rate per Field
  10. Daily Entry Log: Hours, Records per Hour and Error Rate
  11. Data Quality Dashboard: Clean Rate, Target Check and Issue Chart
  12. Phone Numbers and Account Codes: SUBSTITUTE, LEN Checks and Leading Zeros
  13. Double-Keying Check: Comparing Two Entries Field by Field with EXACT
  14. Batch Control Totals: Record Counts, Amount Totals and Hash Totals
  15. Monthly Entry Summary: SUMIFS by Clerk and Document Type, Totals and a Stacked Chart
  16. Records Retention: Lookup of Keep Periods, EDATE and Due-for-Review Flags
  17. Turnaround Times: NETWORKDAYS, a Two-Day Target and the Open Backlog
  18. Check Digits: Catching Typing Errors in Account Numbers with MID and MOD
  19. Final Project – Monthly Records Report: Speed, Error Rate, On-Time Rate and a Status per Team

Buy course — $9.90 See the full course