Clean Messy Data: TRIM, LEFT, MID, CONCAT, Text to Columns

In real work, data rarely arrives clean. A list of customers copied from an email has extra spaces. Names are all capitals. A full name sits in one cell and you need first and last separately. The same customer appears three times. Cleaning data takes up a large part of an office worker's day, and people who can do it fast are valued. This lesson gives you the tools.

We will practise on a small messy list. Type it into a new sheet called Clean, starting at A1.

   A
1  Name
2    asha RAO
3  BEN carter
4  chidi  okafor
5  Divya Nair
6  Asha Rao

Row 2 has spaces at the start, row 4 has two spaces between the words, and the capitals are mixed. Row 2 and row 6 are the same person.

TRIM: remove extra spaces

=TRIM(A2)

TRIM removes spaces at the start and end, and reduces repeated spaces between words to one. Put it in B2 and copy down. Extra spaces are the number one hidden reason why lookups and SUMIF formulas fail, so make TRIM a habit when data comes from outside. Spaces that come from web pages can be a special kind that TRIM misses, which you can remove with SUBSTITUTE(A2, CHAR(160), " ").

PROPER, UPPER and LOWER: fix capitals

=PROPER(TRIM(A2))    -> Asha Rao
=UPPER(A2)           -> ASHA RAO
=LOWER(A2)           -> asha rao

PROPER puts a capital letter at the start of each word. You can put one function inside another, as above. Excel works from the inside out. First TRIM, then PROPER. Be careful with names like “McDonald” or “van der Berg”, which PROPER will change. Check the result.

LEFT, RIGHT and MID: take part of a text

• LEFT(text, n) takes the first n characters.
• RIGHT(text, n) takes the last n characters.
• MID(text, start, n) takes n characters from a starting position.

=LEFT("INV-2024-0042", 3)      -> INV
=MID("INV-2024-0042", 5, 4)    -> 2024
=RIGHT("INV-2024-0042", 4)     -> 0042

Useful for invoice numbers, codes, phone numbers and similar fixed patterns. The result is text, even if it looks like a number. To use it in a calculation, convert it with VALUE(...) or multiply by 1.

FIND, SEARCH and LEN

LEN(text) counts the characters. FIND("-", text) returns the position of a character, and SEARCH does the same without caring about capitals. Combined with LEFT, they let you split text at a separator. To get the first name from Asha Rao:

=LEFT(B2, FIND(" ", B2) - 1)         -> Asha
=MID(B2, FIND(" ", B2) + 1, 50)         -> Rao

FIND returns the position of the space, 5. LEFT takes four characters, one less than that. The MID formula starts one place after the space and takes up to 50 characters, which is more than enough for any surname.

Join text: &, CONCAT and TEXTJOIN

• The & sign joins text: =B2&" "&C2 joins first name, a space and last name.
• CONCAT(a, b, c) joins many cells (Excel 2019 and later; older versions use CONCATENATE).
• TEXTJOIN(", ", TRUE, A2:A6) joins a range with a separator and can skip blanks.

You can join text and numbers: ="Total: "&G13 shows Total: 20150. To format a number inside text, use TEXT: ="Total: "&TEXT(G13, "#,##0").

Text to Columns: split without formulas

Select the column, then Data, Text to Columns. Choose Delimited, press Next, tick the separator that your data uses (space, comma, tab or something else), and Finish. Excel splits each cell into several columns. This is the fastest way to split full names, or the lines of a CSV that were pasted into one column. Make sure there are empty columns to the right, because Excel overwrites what is there.

Flash Fill: let Excel see the pattern

Type the result you want for the first row next to your data, begin typing the second, and Excel often shows a grey preview of the rest. Press Enter to accept. Or use Ctrl+E (Flash Fill). It works with first names, initials, joining and many other simple patterns. It does not update later if the data changes, so use formulas for things that will change.

Find and Replace

Ctrl+H opens Replace. Enter what to find and what to replace it with, then Replace All. You can find a special character, for example to remove all hyphens from phone numbers, by leaving the replace box empty. Use the Options button to match whole cells only and avoid changing too much. Always look at a few results before pressing Replace All, and remember Ctrl+Z.

Remove duplicates

1. Make a copy of your sheet first.
2. Click inside the data and choose Data, Remove Duplicates.
3. Choose the columns that define a duplicate. If every column ticked is the same, the row is a duplicate.
4. Press OK. Excel tells you how many duplicates it removed.

In the Clean list, after TRIM and PROPER, Asha Rao appears twice, in row 2 and row 6. Remove Duplicates would remove one of them, but only if you run it on the cleaned column, not the original with different spaces and capitals. Another way is to flag duplicates and check them by eye. Use =COUNTIF(B$2:B$6, B2)>1 to mark them, or Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.

Handling blanks

• Filter a column and tick (Blanks) to find empty cells.
• Ctrl+G, Special, Blanks selects all empty cells in a range, so you can fill them or delete the rows.
• =IF(A2="", "Missing", A2) marks missing values in a formula.
• COUNTBLANK counts them.

Convert formulas to values

When you have cleaned data using formulas in column B, you often want to keep only the results. Copy column B, then right-click and choose Paste Special, Values. Now the cells hold fixed values, and you can delete the original column A. Do this carefully, and keep a copy of the original sheet so you can go back.

Your task

1. Type the Clean list. In column B, use PROPER(TRIM(A2)) to fix it.
2. Split the cleaned names into first and last name in two ways: with formulas, and with Text to Columns.
3. Use COUNTIF to flag the duplicate, then remove it.
4. Join first and last names back together with & and TEXTJOIN.

How to check it worked

• Row 2 and row 6 both show Asha Rao after cleaning, and the duplicate is detected.
• Row 4 shows Chidi Okafor with a single space.
• You can explain why you keep a copy of the original data.