Tabellab

Shipment Log Cleanup: TRIM, UPPER and PROPER

Lesson 1 of 20Lab

All lessons

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

  • Home → Find & Select → Replace can remove spaces too, but a formula keeps the raw data and repeats the cleanup on tomorrow’s export.
  • When the clean columns are right, copy them and use Paste Special → Values before you delete the raw columns.
Loading the spreadsheet…

Why shipment data needs cleaning before anything else

If you coordinate shipments, you live in spreadsheets: carrier exports, warehouse management system downloads, booking confirmations, invoices and the log your colleagues type by hand. You are expected to answer simple questions fast. How many shipments did Nordline carry this week? What is our on-time rate per carrier? What did the lane to Ottawa cost per kilogram? The formulas for those answers are not hard. The problem is that the data underneath them is rarely clean, and dirty data gives wrong answers without any error message.

This free first lesson of the course tackles that problem directly. You clean the daily shipment log of Harbor Lane Distribution, a fictional home-goods distributor near Toronto. The raw log contains IDs with spaces before and after them, carrier names in all capitals or all lower case and cities typed in every possible way. With three functions, TRIM, UPPER and PROPER, you turn it into a log that can be counted, looked up and summarised. Then you prove the point with COUNTIF: the raw column undercounts a carrier, the clean column gets it right.

The lesson is for logistics coordinators, dispatchers, transport planners, warehouse supervisors and anyone who receives shipment data from more than one source. You need to know how to type a formula and copy it down; the rest is explained step by step. All companies, carriers, IDs and numbers in this course are fictional.

The core idea: computers compare characters, not meanings

To a person, "nordline ", "NORDLINE" and "Nordline" are obviously the same carrier. To Excel they are strings of characters, and a trailing space is a character like any other. Lookups such as XLOOKUP and conditions in COUNTIF, SUMIFS and PivotTables treat "nordline " and "Nordline" as two different values. The result is a carrier that appears twice in a pivot, a lookup that returns #N/A, or a count that is simply too low.

There are three typical problems in shipment logs, and each has a matching function:

  • Extra spaces. Spaces before or after a value, or two spaces between words, come from manual typing, copy and paste from e-mails, and fixed-width exports. TRIM removes spaces at the start and end and reduces any run of spaces between words to a single space.
  • Inconsistent capitals in codes. Shipment IDs like "sh-10421" and "SH-10421" should look identical. UPPER converts every letter to a capital, which is the usual convention for IDs, SKUs and codes.
  • Inconsistent capitals in names. Carrier names and cities read best with a capital at the start of each word. PROPER does exactly that: "swift haul" becomes "Swift Haul", "KINGSTON" becomes "Kingston".

The functions are combined by nesting: =UPPER(TRIM(A2)) first removes the spaces and then capitalises the result. The order does not change the outcome here, but "clean first, then format" is a good habit because later you may add more cleaning steps, such as removing line breaks or replacing special characters, inside the same formula.

COUNTIF and the other IF-family functions are not case-sensitive: =COUNTIF(B2:B7,"Nordline") counts "nordline" and "NORDLINE" as matches. Spaces, however, are never ignored. That is why in this lab the trailing space is what breaks the raw count, and the case cleanup mainly matters for reports, lookups in other tools and plain readability.

Worked example: Harbor Lane Distribution's shipment log

The lab sheet has six shipments. Columns A to C hold the raw values exactly as they came from the export; columns D to F are where you build the clean versions. In the table below, quotation marks show where the hidden spaces are:

RowShipment (raw)Carrier (raw)City (raw)Shipment ID (D)Carrier (E)City (F)
2"  sh-10421""nordline ""mississauga"SH-10421NordlineMississauga
3"SH-10422 ""SWIFT HAUL"" HAMILTON "SH-10422Swift HaulHamilton
4"sh-10423"" bluecargo""london"SH-10423BluecargoLondon
5" Sh-10424""Nordline""ottawa "SH-10424NordlineOttawa
6"sh-10425  ""swift  haul""KINGSTON"SH-10425Swift HaulKingston
7"SH-10426""nordline"" barrie"SH-10426NordlineBarrie

Look at the carriers in column B. Three rows are Nordline: "nordline " with a trailing space in row 2, "Nordline" in row 5 and "nordline" in row 7. Two are Swift Haul: "SWIFT HAUL" in row 3 and "swift  haul" with a double space in row 6. One is Bluecargo with a leading space. After cleaning, column E shows three tidy names, and every count is correct.

The formulas used in the worked example:

CellTaskFormulaResult
D2 (copy to D7)Clean shipment ID=UPPER(TRIM(A2))SH-10421
E2 (copy to E7)Clean carrier name=PROPER(TRIM(B2))Nordline
F2 (copy to F7)Clean city name=PROPER(TRIM(C2))Mississauga
B9Nordline shipments (clean column)=COUNTIF(E2:E7,"Nordline")3
(check)Same count on the raw column=COUNTIF(B2:B7,"Nordline")2, the trailing space in B2 is missed

The difference between the two counts is small in a six-row lab, but it scales. If one in twenty rows of a weekly export has a stray space, a carrier report built on the raw column undercounts every carrier by roughly that share, and the error changes from week to week depending on who typed the data. Figure 1 shows the effect on all three carriers in this log.

COUNTIF on raw vs clean carrier names (fictional log)Raw column BClean column E012323Nordline12Swift Haul01Bluecargoshipments counted; six rows in the log
Figure 1. Shipments per carrier counted with COUNTIF. On the raw column (peach) the stray spaces hide one shipment of each carrier, Bluecargo disappears entirely. The clean column (mint) counts all six rows.

Figure 2 shows what happens inside the nested formulas for two of the rows. The raw ID has two leading spaces, the raw carrier a double space in the middle. TRIM fixes the spacing, then UPPER or PROPER fixes the capitals.

Nest the functions: clean first, then fix the caseRaw valueAfter TRIMFinal value"··sh-10421""sh-10421""SH-10421"TRIMUPPER"swift··haul""swift haul""Swift Haul"TRIMPROPER· = one space character. Formulas: =UPPER(TRIM(A2)) and =PROPER(TRIM(B6))
Figure 2. Two cleanup chains from the lab: the ID goes through TRIM and UPPER, the carrier name through TRIM and PROPER. Each dot stands for a space character.

Step by step: cleaning the log in Excel

Work through these steps in the lab above or in your own shipment export.

  1. Keep the raw data. Never overwrite the original columns. Add clean columns next to them (here D to F) with clear headers such as "Shipment ID", "Carrier" and "City". If something goes wrong, the source is still there.
  2. Clean the shipment IDs. Click D2 and type =UPPER(TRIM(A2)). Press Enter. D2 shows SH-10421.
  3. Clean the carriers. In E2 type =PROPER(TRIM(B2)). E2 shows Nordline.
  4. Clean the cities. In F2 type =PROPER(TRIM(C2)). F2 shows Mississauga.
  5. Copy the formulas down. Select D2:F7 and press Ctrl+D (Cmd+D on a Mac). Alternatively, double-click the fill handle at the bottom right of the selection, which fills down as far as the neighbouring data goes.
  6. Prove that it worked. In a spare cell type =LEN(A2) and =LEN(D2): 10 characters raw, 8 clean. The difference is the two hidden spaces.
  7. Count on the clean column. In B9 type =COUNTIF(E2:E7,"Nordline"). The result is 3. For comparison, =COUNTIF(B2:B7,"Nordline") returns 2.
  8. Freeze the result if needed. If you want to delete the raw columns, first select D2:F7, copy with Ctrl+C, then use Paste Special → Values (Ctrl+Alt+V on Windows, Ctrl+Cmd+V on a Mac). Only then delete columns A to C; otherwise the formulas turn into #REF! errors.
  9. Reuse it tomorrow. Better still, keep the formulas and paste tomorrow's raw export into columns A to C. Turn the range into a table with Ctrl+T and the formulas extend to new rows automatically.
If TRIM seems to do nothing, the culprit is often a non-breaking space (character 160) from a web page or e-mail. TRIM only removes the normal space character. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to convert those spaces first, and wrap the whole thing in CLEAN to remove line breaks: =UPPER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))).

Beyond formulas: other ways to clean

Formulas are the most transparent method, and the one used in the lab, but you should know the alternatives:

  • Find and Replace (Ctrl+H). Quick for one-off fixes, such as replacing "Swift Haul" with "Swift Haul". It changes the data in place and must be repeated on every new export.
  • Flash Fill (Ctrl+E). Type one clean example next to the raw value and press Ctrl+E; Excel guesses the pattern. Handy, but check the results, because it does not update when the source changes.
  • Power Query. Data → From Table/Range opens the Power Query editor, where Transform → Format offers Trim, Clean, UPPERCASE and Capitalize Each Word. The steps are recorded and repeat with one click on Refresh, which suits a daily export.
  • Data validation at the source. The best cleanup is the one you never need. A drop-down list of carriers (Data → Data Validation → List) in the input sheet stops new variants from being typed.

Excel for logistics coordinators: shipment log, freight costs and on-time delivery analysis
The course builds a complete logistics toolkit on the data of one fictional distributor, starting with a clean shipment log.

Common mistakes and how to spot them

Data cleaning goes wrong in predictable ways:

  • Counting on the raw column. The most common error, and the one the lab is built around. Spot it: compare COUNTIF on raw and clean columns, or =SUM(COUNTIF(E2:E7,{"Nordline","Swift Haul","Bluecargo"})) against the number of rows.
  • PROPER on IDs. =PROPER(TRIM(A2)) turns "sh-10421" into "Sh-10421", which still differs from "SH-10421" in a case-sensitive tool. Use UPPER for codes.
  • PROPER on names with special capitals. PROPER turns "McAllister" into "Mcallister" and an abbreviation such as "ABC Freight" into "Abc Freight". For brand names with unusual capitals, keep a small mapping table and look them up instead.
  • Deleting the raw columns too early. The clean formulas refer to the raw cells; deleting them gives #REF!. Paste values first, or keep the raw columns hidden.
  • Thinking TRIM removes all spaces. It keeps one space between words, which is what you want for "Swift Haul". To remove every space, for example in a postal code, use =SUBSTITUTE(A2," ","").
  • Invisible characters left over. If LEN of a clean value is still larger than the visible characters, look for non-breaking spaces or line breaks and use SUBSTITUTE and CLEAN.

Quick reference: functions and shortcuts

Function / shortcutWhat it does
TRIM(text)Removes leading and trailing spaces and reduces runs of spaces between words to one.
UPPER(text)Converts all letters to capitals, good for IDs and codes.
PROPER(text)Capitalises the first letter of each word, good for names and cities.
LOWER(text)Converts all letters to lower case, useful for e-mail addresses.
COUNTIF(range, criteria)Counts matching cells; not case-sensitive, but spaces count.
LEN(text)Counts characters; LEN of raw vs clean shows hidden spaces.
SUBSTITUTE(text, old, new)Replaces characters, for example non-breaking spaces CHAR(160) that TRIM ignores.
CLEAN(text)Removes non-printing control characters such as line breaks from exports.
EXACT(a, b)Case-sensitive comparison; TRUE only if two texts are identical.
Ctrl+D / Cmd+DFills a formula down the selected range.
Ctrl+Alt+V (Win) / Ctrl+Cmd+V (Mac)Opens Paste Special, then choose Values to freeze the cleaned text.
Ctrl+HFind and Replace, a manual alternative for one-off fixes.

Real-world uses in logistics

Clean text columns are the foundation of almost every logistics report:

  • Carrier performance reports. On-time rates and cost per kilogram per carrier only work if every carrier name is written one way.
  • Matching invoices to shipments. A freight invoice lists "SH-10424" while the log says " Sh-10424"; a lookup on the cleaned ID finds the match.
  • Lane analysis. Grouping cost and transit time by destination city needs "Ottawa", not "ottawa " and "OTTAWA" as three cities.
  • Merging exports from two systems. A warehouse system and a carrier portal format names differently; cleaning both to the same convention makes them joinable.
  • Preparing data for a PivotTable or Power BI. Duplicated categories in a pivot are almost always a cleaning problem upstream.

What you will build in the rest of this course

Lesson 1 is free. The other nineteen lessons are part of the paid course (a one-time $9.90) and use the same fictional distributor to build a full logistics toolkit.

Freight Cost per Shipment, per kg and per Pallet. You turn freight invoices into cost per kilogram and per pallet and learn why the true average cost per kg is a weighted average, not the average of the rates.

Carrier Rate Card: Weight Brackets with XLOOKUP. You price shipments from a rate card by finding the right weight bracket and applying the minimum charge.

Dimensional Weight vs Actual Weight with MAX. You calculate the volumetric weight of parcels, take the higher of actual and volumetric weight and see how bulky light items get priced.

On-Time Delivery Rate per Carrier with COUNTIFS. You mark deliveries as on time or late and calculate each carrier's OTD rate, with the clean carrier names from today.

Transit Days in Working Days: NETWORKDAYS and Holidays. You measure transit times in working days, skip a public holiday and compare each shipment with its lane target.

Pallets, Trailers and Fill Rate with ROUNDUP. You convert store orders into pallets and trailers and work out how full each trailer will be.

Fuel Surcharge per Kilometre on Truckload Invoices. You derive a weekly surcharge per kilometre from a fuel price table and check invoices against it.

Warehouse Pick Productivity: Lines per Hour. You measure pickers' lines per hour against a target and highlight who needs support.

Stock Movements: Running Balance, Reorder Flag and Cycle Count. You keep a stock card with a running balance, a reorder flag and a cycle-count check.

Carrier Scorecard: OTD, Cost per kg and Ranking. You combine service and cost into a quarterly carrier scorecard with a ranking and a bar chart for the review meeting.

Logistics Dashboard: OTD Trend, Cost per Shipment and Highlights. You finish with a half-year dashboard of on-time performance and cost per shipment, a trend chart and automatic highlights for months below target.

Summary

Every logistics report rests on its text columns. In this lesson you kept the raw shipment log untouched and built clean columns next to it: =UPPER(TRIM(A2)) for IDs, =PROPER(TRIM(B2)) for carriers and cities. The payoff showed up immediately: COUNTIF finds 3 Nordline shipments in the clean column against 2 in the raw one. You also know what to do when TRIM is not enough, how to freeze values safely and how to stop dirty data at the source.

Now scroll up to the lab and clean the Harbor Lane log yourself. The Coach checks the last row of each clean column and the Nordline count in B9, and tells you if you counted the raw column by mistake.

Frequently asked questions

How do I remove extra spaces in Excel?

Use =TRIM(A2). It removes spaces at the start and end of the text and reduces several spaces between words to one. Copy the formula down and paste as values if you want to replace the original.

Why does TRIM not remove some spaces?

Text copied from web pages often contains non-breaking spaces (character 160), which TRIM ignores. Replace them first with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

What is the difference between UPPER and PROPER in Excel?

UPPER converts every letter to a capital, which suits IDs and codes. PROPER capitalises the first letter of each word and lowers the rest, which suits names and cities.

Is COUNTIF case-sensitive?

No. COUNTIF counts "nordline" and "NORDLINE" as the same, but it does not ignore spaces, so "nordline " with a trailing space is not counted. Use EXACT inside SUMPRODUCT if you need a case-sensitive count.

How can I combine TRIM and UPPER in one formula?

Nest them: =UPPER(TRIM(A2)). Excel first removes the extra spaces and then converts the result to capitals.

How do I replace formulas with their cleaned values?

Select the clean cells, copy them with Ctrl+C and use Paste Special and Values (Ctrl+Alt+V on Windows). After that you can delete the raw columns without getting #REF! errors.

Keep going: Excel for Logistics Coordinators: Shipments, Freight Costs & On-Time Delivery

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. Freight Cost per Shipment, per kg and per Pallet
  2. Carrier Rate Card: Weight Brackets with XLOOKUP
  3. Dimensional Weight vs Actual Weight with MAX
  4. On-Time Delivery Rate per Carrier with COUNTIFS
  5. Transit Days in Working Days: NETWORKDAYS and Holidays
  6. Pallets, Trailers and Fill Rate with ROUNDUP
  7. Fuel Surcharge per Kilometre on Truckload Invoices
  8. Warehouse Pick Productivity: Lines per Hour
  9. Stock Movements: Running Balance, Reorder Flag and Cycle Count
  10. Carrier Scorecard: OTD, Cost per kg and Ranking
  11. Logistics Dashboard: OTD Trend, Cost per Shipment and Highlights
  12. Dock Appointments: Late Arrivals, Unload Times & Door Hours
  13. Freight Invoice Audit: Expected Charges, Overbilling & Disputes
  14. Open Shipments Report: Days Since Pickup, Overdue Flags & Counts by Status
  15. Freight Claims: Claims per 100 Shipments, Amounts Paid & Recovery Rate
  16. LTL or Full Truckload? Comparing Two Quotes per Order & the Break-Even Weight
  17. Shipping Volume by Weekday: Day Names, AVERAGEIFS & Dock Staffing
  18. Customer Delivery Profiles: XLOOKUP, Not-Found Values & Special Requirements
  19. Final Project: Monthly Lane Report with SUMIFS, OTD Targets, Flags and a Chart

Buy course — $9.90 See the full course