Level 4 project: clean up a shop export

You need: LEFT, RIGHT and MID, FIND, TRIM, DATE, NETWORKDAYS and ISOWEEKNUM, and SUMIF with wildcards.

The situation

A small online shop exports its orders every week. When the file is opened, everything lands in Excel as text, and the customer names were typed by hand, sometimes with extra spaces and sometimes without. The workbook has three sheets:

• Raw: the export. Customer as "Last name, First name", an order code like UK-2026-0412-B (country, year, running number, a letter code), the order date as text, the delivery date as a real date, and the amount.
• Holidays: the bank holidays around the turn of the year.
• Cleaned: this is where you clean up the first order. You fill in the yellow cells.

The project

Task The shop export on the Raw sheet is plain text: names as "Last name, First name" with extra spaces, and the order date as text. Clean up the first order (row 2): last name in A2 and first name in B2 without stray spaces, in C2 the year from the order code as a number, in D2 the order date as a real date, in E2 the working days from order to delivery (counting both days, without the bank holidays on the Holidays sheet), in F2 the ISO week number and in G2 the total of all amounts for this customer in the export.

	A	B	C	D	E	F	G	H
1	Last name	First name	Year	Order date	Working days to delivery	Week	Customer total
2
3
4

CleanedRawHolidays

What matters here

Split first, then trim

The comma separates last and first name. FIND(",",Raw!A2) gives its position, LEFT takes everything before it and MID everything after. The spaces come along too, which is why TRIM goes around the outside. TRIM removes spaces at the start and end and turns runs of spaces between words into a single one. You cannot see the extra spaces on the practice sheet, just as in many real exports. They only show up when a comparison or lookup fails.

The year as a number

MID(Raw!B2,4,4) returns "2026", but as text. It looks like a number, yet a comparison such as =C2=2026 returns FALSE. VALUE turns it into a real number. --MID(…) or MID(…)*1 work just as well.

Build a real date from text

"2026-12-22" in Raw!C2 is text, and no date function can work with it. The text has fixed positions, though: year first, month from character 6, day at the end. DATE puts the three pieces together: =DATE(LEFT(Raw!C2,4),MID(Raw!C2,6,2),RIGHT(Raw!C2,2)). DATE converts the text pieces to numbers itself. This is safer than DATEVALUE, whose result depends on the date settings of the computer, and it avoids any mix-up between day-first and month-first formats.

Working days with a holiday list

NETWORKDAYS counts Monday to Friday between two dates, including both ends, and leaves out the dates in its third argument. From 22 Dec to 30 Dec 2026 there are seven weekdays. The Holidays sheet lists the bank holidays for England and Wales: Christmas Day on Friday 25 Dec, and Boxing Day, which falls on a Saturday in 2026 and is therefore moved to Monday 28 Dec as a substitute day. That leaves 5 working days. Scotland and Northern Ireland have their own lists, and in the US you would use federal holidays such as Christmas Day instead. Whatever applies to you goes on the Holidays sheet.

ISO week numbers

The workbook uses ISO weeks, as is common in the UK and the rest of Europe: weeks start on Monday, and week 1 is the week containing the first Thursday of the year. ISOWEEKNUM returns that. Many US teams use WEEKNUM instead, where weeks start on Sunday and the week containing 1 January is always week 1. Around New Year the two give different answers, so pick one and use it everywhere. If your team works with US weeks, use WEEKNUM in your own files.

A total despite different spellings

The same customer appears three times in the export, written differently each time, so a plain comparison misses her. With wildcards around the cleaned last name, SUMIF adds the amounts of every row that contains "Turner" anywhere: =SUMIF(Raw!A2:A7,"*"&A2&"*",Raw!E2:E7). Once two customers share a last name, that is no longer enough. Then you need a cleaned column with the full name, or better still a customer number.

Helper calculations

You may use empty cells for your own intermediate steps, for example put the position of the comma in a helper cell and refer to it in A2 and B2. Only the yellow cells are checked.

If it does not work

• The message names the first cell that is not right yet. Everything before it is correct.
• If A2 looks right but is marked wrong, TRIM is missing. A leading space is invisible on the sheet.
• If B2 still includes the comma, MID starts one character too early. Use FIND(…)+1.
• If C2 shows 2026 and is still marked wrong, it is text. Wrap MID in VALUE.
• If E2 is two too high, the range Holidays!A2:A4 is missing as the third argument.
• If F2 is only right with this data, you are probably using WEEKNUM instead of ISOWEEKNUM.

FAQ