--- title: "How to clean data in Excel with formulas | PracticeExcel" description: "Fix messy exports with formulas. Remove extra spaces, fix capitals, split names and codes, join columns, find duplicates and turn text into real numbers." canonical_url: "https://practiceexcelonline.com/learn/clean-data-in-excel" locale: en meta: "og:description": "Fix messy exports with formulas. Remove extra spaces, fix capitals, split names and codes, join columns, find duplicates and turn text into real numbers." "og:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_How%2Bto%2Bclean%2Bdata%2Bin%2BExcel%2Bwith%2Bformulas%2Cdescription_~Rml4IG1lc3N5IGV4cG9ydHMgd2l0aCBmb3JtdWxhcy4gUmVtb3ZlIGV4dHJhIHNwYWNlcywgZml4IGNhcGl0YWxzLCBzcGxpdCBuYW1lcyBhbmQgY29kZXMsIGpvaW4gY29sdW1ucywgZmluZCBkdXBsaWNhdGVzIGFuZCB0dXJuIHRleHQgaW50byByZWFsIG51bWJlcnMu%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9jbGVhbi1kYXRhLWluLWV4Y2VsIg%2Cs_yVtqrOCs8fBDIMnn.png" "og:image:height": "600" "og:image:type": image/png "og:image:width": "1200" "og:title": "How to clean data in Excel with formulas | PracticeExcel" "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_How%2Bto%2Bclean%2Bdata%2Bin%2BExcel%2Bwith%2Bformulas%2Cdescription_~Rml4IG1lc3N5IGV4cG9ydHMgd2l0aCBmb3JtdWxhcy4gUmVtb3ZlIGV4dHJhIHNwYWNlcywgZml4IGNhcGl0YWxzLCBzcGxpdCBuYW1lcyBhbmQgY29kZXMsIGpvaW4gY29sdW1ucywgZmluZCBkdXBsaWNhdGVzIGFuZCB0dXJuIHRleHQgaW50byByZWFsIG51bWJlcnMu%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9jbGVhbi1kYXRhLWluLWV4Y2VsIg%2Cs_yVtqrOCs8fBDIMnn.png" --- > Index of every page on this site: [llms.txt](https://practiceexcelonline.com/llms.txt) # **How to clean data in Excel with formulas** Fix messy exports with formulas. Remove extra spaces, fix capitals, split names and codes, join columns, find duplicates and turn text into real numbers. By Nicolaj Gajhede, founder of PracticeExcel · Updated October 29, 2026 · 12 min read Almost no data arrives in Excel ready to use. A customer list exported from a CRM has names in capitals, codes with spaces at the end and phone numbers in five different formats. A report from the finance system has numbers that SUM refuses to add up. A sign-up form has the same person three times. Before you can total, look up or summarize anything, the data has to be cleaned. You can clean data by hand, one cell at a time, and for ten rows that is fine. For a thousand rows, or for an export you will get again next month, formulas are faster, more reliable and repeatable. This guide covers the clean-up jobs that come up most, the formulas that do each one, and an order to do them in so you don't create new problems while fixing the old ones. ## Before you start: keep the original The first rule of cleaning data is not to overwrite it. Keep the raw export untouched, either on its own sheet or in its original columns, and do the cleaning in **helper columns** next to it: a column of formulas that reads the messy value and returns the clean one. Helper columns have three advantages over editing cells directly: - **You can see what changed.** The raw value and the clean value sit side by side, so you can check any row. - **Nothing is lost.** If a formula turns out to be wrong, you fix it and fill it down again. The original is still there. - **It works again.** Next month, paste the new export into the raw columns and the helper columns clean it the same way. When you are happy with the result, you can turn the formulas into fixed values with [**Paste Special**](https://practiceexcelonline.com/shortcuts/paste-special) and choose Values, then delete the raw columns if you no longer need them. Do that last, not first. ## Find the problems first You can't fix what you haven't spotted, and dirty data often looks clean. A few quick checks show you what you are dealing with: - **Open the filter list.** Turn on filters with [**Ctrl+Shift+L**](https://practiceexcelonline.com/shortcuts/toggle-filter) and open the drop-down on a column of categories. Every spelling is listed separately, so "New York", "New York " and "new york" show up as three entries. - **Count the characters.** LEN returns the number of characters in a cell, spaces included. A value that is longer than it looks has hidden spaces: B2=LEN(A2) | | A | B | | --- | --- | --- | | 1 | Last name | | | 2 | Lopez | **6** | LEN counts 6 characters in a 5-letter name. The sixth is a space at the end that you cannot see in the cell. - **Look at the alignment.** Unless someone changed it, numbers and dates sit at the right of the cell and text at the left. A column of amounts with a few values on the left has numbers stored as text. - **Compare COUNT with COUNTA.** On a column that should be all numbers, `**=COUNT(B2:B500)**` counts the numbers and `**=COUNTA(B2:B500)**` counts everything that isn't empty. If they differ, some of the values are text. - **Count the blanks.** `**=COUNTBLANK(B2:B500)**` tells you how many values are missing. The [**COUNTBLANK exercise**](https://practiceexcelonline.com/practice/count/countblank-empty-cells) drills it. Five minutes of checks tells you which of the sections below you need. ## Extra spaces: TRIM Extra spaces are the most common problem in exported data and the most common reason a lookup returns `**#N/A**` for a value you can see. "A-104 " with a space at the end does not match "A-104". TRIM removes every space at the start and end of a text and turns runs of spaces between words into one: B2=TRIM(A2) | | A | B | | --- | --- | --- | | 1 | Raw name | | | 2 | Maria Lopez | **Maria Lopez** | TRIM removes the spaces before and after the text and leaves one space between the words. Practice it in [**removing extra spaces with TRIM**](https://practiceexcelonline.com/practice/trim-proper-upper/trim-extra-spaces). You can also put TRIM inside another formula rather than in a helper column, for example `**=VLOOKUP(TRIM(E1),A2:C50,3,FALSE)**`, when only the value you look up is dirty. Two kinds of "space" TRIM does not remove: - **Non-breaking spaces**, which come from web pages and some systems. They look identical to a normal space, but they are a different character, number 160. Replace them with a normal space first and then trim: `**=TRIM(SUBSTITUTE(A2,CHAR(160)," "))**`. - **Line breaks and other invisible characters**, which come from text copied out of emails or multi-line form fields. CLEAN removes them: `**=TRIM(CLEAN(A2))**`. If TRIM doesn't seem to work, LEN before and after tells you whether one of these is the cause. ## Capital letters: PROPER, UPPER and LOWER Exports often have names in capitals or in the random case people typed them in. Three functions fix the case of a text: - **PROPER** gives every word a capital first letter and makes the rest lowercase: maria lopez becomes Maria Lopez. - **UPPER** makes everything uppercase, which suits codes and IDs. - **LOWER** makes everything lowercase, which suits email addresses. They combine well with TRIM. Put TRIM inside, so the spaces are gone before the case is fixed: B2=PROPER(TRIM(A2)) | | A | B | | --- | --- | --- | | 1 | Raw name | | | 2 | mARIA lOPEZ | **Maria Lopez** | TRIM runs first and removes the double space, then PROPER gives every word one capital letter. PROPER is not perfect for names. It turns McDONALD into Mcdonald and O'NEIL into O'Neil, which happens to be right, and it capitalizes every word, so "van der Berg" becomes Van Der Berg. For a customer list that is usually close enough; for anything printed on a certificate or an envelope, check the exceptions by hand. Practice the case functions in [**fixing the case of names**](https://practiceexcelonline.com/practice/trim-proper-upper/proper-capitalize-names) and [**cleaning a code with UPPER and TRIM**](https://practiceexcelonline.com/practice/trim-proper-upper/upper-trim-clean-codes). ## Taking text apart at fixed positions: LEFT, RIGHT and MID Codes often have a structure: the first three characters are a site, the last four are a year, a part number sits in the middle. When the positions are always the same, three functions cut the text up: - **LEFT** takes characters from the start: `**=LEFT(A2,3)**`. - **RIGHT** takes characters from the end: `**=RIGHT(A2,4)**`. - **MID** takes characters from the middle, from a start position: `**=MID(A2,5,4)**` takes four characters starting with the fifth. B2`=LEFT(1A2,23)` | | A | B | | --- | --- | --- | | 1 | Order code | | | 2 | NYC-20441 | **NYC** | **In plain words:** Take 2the first 3 characters of 1the order code in A2. The first three characters of every order code are the site, so LEFT with 3 takes them out. The three [**LEFT, RIGHT and MID exercises**](https://practiceexcelonline.com/practice/left-right-mid) start with [**the prefix**](https://practiceexcelonline.com/practice/left-right-mid/left-first-characters), then [**the last digits**](https://practiceexcelonline.com/practice/left-right-mid/right-last-characters) and [**a year in the middle**](https://practiceexcelonline.com/practice/left-right-mid/mid-text-from-middle). One thing to know: all three return text, even when the characters are digits. `**=RIGHT(A2,4)**` on NYC-2044 returns the text "2044", not the number. If you need to calculate with it, wrap it in VALUE: `**=VALUE(RIGHT(A2,4))**`. ## Taking text apart at a character: FIND and SEARCH Names, email addresses and many codes don't have fixed positions. The first name is everything before the first space, wherever that is; the domain is everything after the @. For those, you first find the position of the character, then cut at it. FIND returns the position of one text inside another: `**=FIND("@",A2)**` is 12 for [**maria.lopez@northwind.com**](mailto:maria.lopez@northwind.com), because the @ is the 12th character. Combine it with LEFT to take everything before the character: B2`=LEFT(1A2,2FIND(" ",A2)-1)` | | A | B | | --- | --- | --- | | 1 | Full name | | | 2 | Maria Lopez | **Maria** | **In plain words:** Take 2everything before the space from the start of 1the full name in A2. FIND returns 6, the position of the space, so LEFT takes the 5 characters before it. The `**-1**` matters: without it, LEFT would include the space itself. To take everything after a character, combine FIND with MID and start one character later: B2`=MID(1A2,2FIND("@",A2)+1,3100)` | | A | B | | --- | --- | --- | | 1 | Email | | | 2 | maria.lopez@northwind.com | **northwind.com** | **In plain words:** From 1the email in A2, take 3up to 100 characters 2starting just after the @. MID starts one character after the @ and takes up to 100 characters, which is everything that is left. The 100 is just "more characters than there could be". MID stops at the end of the text, so any number big enough works. These are some of the most common text formulas in office work, and the [**@ in an email**](https://practiceexcelonline.com/practice/find-search/find-position-of-character), [**name before the @**](https://practiceexcelonline.com/practice/find-search/left-find-text-before-character) and [**domain after the @**](https://practiceexcelonline.com/practice/find-search/mid-find-text-after-character) exercises take you through them in order. SEARCH does the same as FIND but ignores case, and it understands wildcards. Use FIND when the character is exact, such as a space, a dash or an @, and SEARCH when you are looking for a word that may be in any case. ### TEXTBEFORE and TEXTAFTER Excel for Microsoft 365 has two functions that do the FIND-and-cut in one step: `**=TEXTBEFORE(A2," ")**` returns the text before the first space, and `**=TEXTAFTER(A2,"@")**` the text after the @. They are easier to read and easier to get right. The catch is the same as for every new function: older versions show `**#NAME?**`. Practice them in [**taking the first name with TEXTBEFORE**](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/textbefore-first-name) and [**flipping "Last, First" with TEXTAFTER**](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/textafter-flip-names). ### Splitting without formulas For a one-off clean-up, two built-in tools can be faster than formulas: - **Text to Columns** (Data tab) splits a column at a character, such as a comma or a space, into several columns in one go. - **Flash Fill** (Ctrl+E) watches you type the result for the first row or two and fills in the rest by copying the pattern. Both change the data once. They don't update when the source changes, and Flash Fill can guess the pattern wrong on unusual rows, so check the result. For data you clean every month, formulas are the better investment. ## Replacing and removing characters: SUBSTITUTE SUBSTITUTE replaces every occurrence of one text with another. Replace it with nothing, `**""**`, and it removes the characters: B2`=SUBSTITUTE(1A2,2"-",3"")` | | A | B | | --- | --- | --- | | 1 | Phone | | | 2 | 555-201-3344 | **5552013344** | **In plain words:** In 1the phone number in A2, replace 2every dash with 3nothing. SUBSTITUTE replaces every dash with nothing, which leaves only the digits. Practice it in [**removing the dashes with SUBSTITUTE**](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/substitute-remove-characters). To remove several different characters, nest one SUBSTITUTE inside another: `**=SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")**` removes both kinds of parentheses. SUBSTITUTE is case-sensitive and replaces every match unless you give it a fourth argument for which one to replace. Find and Replace (Ctrl+H) does the same job without a formula, on the cells themselves, so use it on a copy. ## Joining text: & and TEXTJOIN Cleaning is often about putting text together as much as taking it apart: a full name from first and last name, an address on one line, a key built from two columns for a lookup. The `**&**` operator joins texts: `**=A2&" "&B2**` puts a space between a first and a last name. Practice it in [**joining first and last name with &**](https://practiceexcelonline.com/practice/join-text/join-names-ampersand). It is fine for two or three parts. For more parts, or when some of them may be empty, TEXTJOIN is better. It takes a separator, TRUE or FALSE for whether to skip empty cells, and the cells to join: E2`=TEXTJOIN(1", ",2TRUE,3A2:C2)` | | A | B | C | D | E | | --- | --- | --- | --- | --- | --- | | 1 | Street | Unit | City | | | | 2 | 12 Main St | | Springfield | | **12 Main St, Springfield** | **In plain words:** Join 3the address parts in A2:C2 with 1a comma and a space between them, 2skipping the empty ones. TEXTJOIN puts a comma and a space between the parts, and TRUE skips the empty unit cell. With `**&**`, the empty unit would have left a double comma: "12 Main St, , Springfield". The [**address exercise**](https://practiceexcelonline.com/practice/join-text/textjoin-address) drills TEXTJOIN with an empty part. Don't use Merge & Center to put two cells together. It keeps only the first value and throws the rest away. The [**guide to merging cells**](https://practiceexcelonline.com/learn/merge-cells-excel) explains what merging does, how to undo it in a messy export and when a formula is the better choice. ## Numbers stored as text This is the most expensive data problem, because it gives wrong totals without any error. Systems often export numbers as text, and copying from a web page or a PDF does the same. The cell shows 350, but Excel treats it like a word: B5=SUM(B2:B4) | | A | B | | --- | --- | --- | | 1 | Item | Amount | | 2 | Paper | 1200 | | 3 | Toner | 350 | | 4 | Labels | 800 | | 5 | | **2000** | The 350 in B3 is text, not a number, so SUM skips it. The total is 2,000 instead of 2,350, and no error shows. Signs that a column has numbers stored as text: the values sit at the left of the cell, a small green triangle appears in the cell's corner, COUNT is lower than COUNTA, and SUM, SUMIF and lookups give results that are a little off. Ways to fix them: - **The warning menu.** Select the cells with the green triangle, click the warning icon and choose Convert to Number. Quick for one column. - **Text to Columns.** Select the column, choose Data > Text to Columns and click Finish without changing anything. Excel reads every value again and converts the ones that look like numbers. - **VALUE in a helper column.** `**=VALUE(B3)**` returns the number. It also works on text that needs cleaning first, such as `**=VALUE(TRIM(B3))**`. - **A calculation.** Multiplying by 1 or adding 0 converts a text number, so `**=B3*1**` works too, but VALUE says more clearly what you meant. Watch out for decimal separators. Text like 1.250,50 from a European system is not a number to a US Excel, and VALUE returns `**#VALUE!**`. Replace the separators with SUBSTITUTE first, or import the file with the right locale. Dates have the same problem, and the same signs. Our [**guide to dates in Excel**](https://practiceexcelonline.com/learn/excel-dates) shows how to rebuild a text date with DATE, LEFT, MID and RIGHT. ## Duplicates The same customer twice, the same invoice imported twice, the same email signed up on three forms. Duplicates inflate counts and totals, and a lookup on a duplicated key returns the first match without warning. The safest way to find them is a COUNTIF helper column that counts how many times each value appears in the whole column: B4`=COUNTIF(1$A$2:$A$6,2A4)` | | A | B | | --- | --- | --- | | 1 | Email | | | 2 | ana@example.com | | | 3 | ben@example.com | | | 4 | ana@example.com | **2** | | 5 | cleo@example.com | | | 6 | dev@example.com | | **In plain words:** Count how many times 2the email in A4 appears in 1the whole list in A2:A6. ana@example.com appears twice, so its count is 2. Fill the formula down the column and every count above 1 is a duplicate. The `**$**` signs keep the range fixed when you fill down, while A4 moves to each row. Filter the helper column for values above 1 and you see every duplicate, with the rest of each row next to it, so you can decide which one to keep. To see the duplicates in the data itself instead of a helper column, color the rows with a conditional formatting rule. The [**conditional formatting guide**](https://practiceexcelonline.com/learn/conditional-formatting-excel) shows the COUNTIF rule that does it. Two other tools: - **Data > Remove Duplicates** deletes duplicate rows in place, keeping the first one. It is fast and permanent, so use it on a copy and check the count it reports. - **UNIQUE** in Excel 365 returns the list without duplicates as a new range: `**=UNIQUE(A2:A500)**`. It is ideal for building a clean list of categories or customers to summarize by. Before you look for duplicates, clean the column first. " [**ana@example.com**](mailto:ana@example.com)" and " [**Ana@example.com**](mailto:Ana@example.com) " are not duplicates to COUNTIF until TRIM and LOWER have made them the same. ## Inconsistent categories A region column with "NY", "New York" and "N.Y." is three regions to SUMIF and to a pivot table. TRIM and PROPER fix the spacing and case, but they can't know that NY means New York. For that, build a small mapping table: every variant in one column, the clean name in the next. Then a lookup replaces each variant with its clean name: `**=XLOOKUP(TRIM(B2),$H$2:$H$20,$I$2:$I$20,B2)**`. The last argument returns the original value when it is not in the table, so new variants show up instead of disappearing. Whenever the filter list shows a new spelling, add it to the table. Our [**lookup guide**](https://practiceexcelonline.com/learn/vlookup-vs-xlookup-vs-index-match) explains XLOOKUP and its alternatives. Once the column is clean, keep it that way. A [**drop-down list**](https://practiceexcelonline.com/learn/drop-down-list-excel) with the clean names means nobody can type a new variant in the first place. ## A clean-up order that works The order matters, because some steps depend on others. Duplicates can't be found before the spaces are gone, and numbers can't be converted while they still contain currency signs. This order works for most exports: 1. **Copy the raw data** to its own sheet and leave it alone. 2. **Remove invisible characters and extra spaces** with CLEAN, SUBSTITUTE for CHAR(160), and TRIM. 3. **Fix the case** with PROPER, UPPER or LOWER. 4. **Split columns** that hold more than one thing, such as full names, codes with parts, or city and ZIP code in one cell. 5. **Convert numbers and dates** stored as text into real numbers and dates. 6. **Map inconsistent categories** to one spelling with a lookup table. 7. **Find and handle duplicates**, now that equal values really are equal. 8. **Check the result**: COUNT against COUNTA, a total against the source system, and a few rows by hand. 9. **Paste as values** if the cleaned data is going somewhere else, and keep the workbook with the formulas for next time. ## Cleaning data in Excel tests Data cleaning is a standard part of tests for analyst, admin and operations roles, because it is what those jobs involve every day. Typical tasks: - **"Remove the extra spaces from the names in column A."** TRIM in a helper column, filled down. - **"Show every name with a capital first letter."** PROPER, often with TRIM inside. - **"Extract the first name," or "the domain of each email."** LEFT or MID with FIND, or TEXTBEFORE and TEXTAFTER if the test's Excel has them. - **"Create an ID from the region code and the order number."** `**&**` or TEXTJOIN. - **"Why is this total wrong?"** Numbers stored as text, or a duplicate row. Check with COUNT, COUNTA and COUNTIF. Tests usually check every row, and the messy rows are put there on purpose: a name with no space, an email in capitals, a code one character shorter than the rest. Before you move on, look at the edge cases in your result, not just the first row. The test pages below include cleaning questions in the format the [**data analyst test**](https://practiceexcelonline.com/tests/excel-test-for-data-analysts) uses. ## Summary Clean data in helper columns next to the original, so you can see every change and repeat it next month. Find the problems first with filters, LEN, COUNT and COUNTA. TRIM removes spaces, PROPER, UPPER and LOWER fix the case, LEFT, RIGHT and MID cut at fixed positions, and FIND or SEARCH tell you where to cut when the positions vary. SUBSTITUTE removes characters, `**&**` and TEXTJOIN put text together, and VALUE turns text into numbers. Find duplicates with COUNTIF once the values are clean, and paste as values only at the end. The exercises below go through each of these on small, deliberately messy sheets, in the order the guide uses them. ![Nicolaj Gajhede](https://practiceexcelonline.com/authors/nicolaj-gajhede.webp) **About the author** ## **Nicolaj Gajhede**, founder of PracticeExcel I'm a software developer, and I built PracticeExcel to help others get comfortable with Excel, and the best way to get there is to work through the formulas yourself. [Nicolaj Gajhede on LinkedIn](https://www.linkedin.com/in/nicolaj-gajhede/) [More about Nicolaj Gajhede](https://practiceexcelonline.com/about#nicolaj-gajhede) ## **Practice what this guide covers** Graded exercises in a real grid, in the order the guide uses them. - [**Remove extra spaces with TRIM**TRIM, PROPER and UPPER →](https://practiceexcelonline.com/practice/trim-proper-upper/trim-extra-spaces) - [**Fix the case of names with PROPER**TRIM, PROPER and UPPER →](https://practiceexcelonline.com/practice/trim-proper-upper/proper-capitalize-names) - [**Clean a code with UPPER and TRIM**TRIM, PROPER and UPPER →](https://practiceexcelonline.com/practice/trim-proper-upper/upper-trim-clean-codes) - [**Take the prefix with LEFT**LEFT, RIGHT and MID →](https://practiceexcelonline.com/practice/left-right-mid/left-first-characters) - [**Take the last digits with RIGHT**LEFT, RIGHT and MID →](https://practiceexcelonline.com/practice/left-right-mid/right-last-characters) - [**Take the year from the middle with MID**LEFT, RIGHT and MID →](https://practiceexcelonline.com/practice/left-right-mid/mid-text-from-middle) - [**Find the @ in an email with FIND**FIND and SEARCH →](https://practiceexcelonline.com/practice/find-search/find-position-of-character) - [**Take the name before the @**FIND and SEARCH →](https://practiceexcelonline.com/practice/find-search/left-find-text-before-character) - [**Take the domain after the @**FIND and SEARCH →](https://practiceexcelonline.com/practice/find-search/mid-find-text-after-character) - [**Remove the dashes with SUBSTITUTE**SUBSTITUTE, TEXTBEFORE and TEXTAFTER →](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/substitute-remove-characters) - [**Take the first name with TEXTBEFORE**SUBSTITUTE, TEXTBEFORE and TEXTAFTER →](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/textbefore-first-name) - [**Flip "Last, First" with TEXTAFTER**SUBSTITUTE, TEXTBEFORE and TEXTAFTER →](https://practiceexcelonline.com/practice/substitute-textbefore-textafter/textafter-flip-names) - [**Join first and last name with &**Joining text with & and TEXTJOIN →](https://practiceexcelonline.com/practice/join-text/join-names-ampersand) - [**Join an address with TEXTJOIN**Joining text with & and TEXTJOIN →](https://practiceexcelonline.com/practice/join-text/textjoin-address) - [**Count empty cells with COUNTBLANK**COUNT, COUNTA and COUNTBLANK →](https://practiceexcelonline.com/practice/count/countblank-empty-cells) ### **Function pages** - [**TRIM**](https://practiceexcelonline.com/functions/trim) - [**PROPER**](https://practiceexcelonline.com/functions/proper) - [**UPPER**](https://practiceexcelonline.com/functions/upper) - [**LEFT**](https://practiceexcelonline.com/functions/left) - [**RIGHT**](https://practiceexcelonline.com/functions/right) - [**MID**](https://practiceexcelonline.com/functions/mid) - [**FIND**](https://practiceexcelonline.com/functions/find) - [**SEARCH**](https://practiceexcelonline.com/functions/search) - [**SUBSTITUTE**](https://practiceexcelonline.com/functions/substitute) - [**TEXTJOIN**](https://practiceexcelonline.com/functions/textjoin) - [**LEN**](https://practiceexcelonline.com/functions/len) - [**UNIQUE**](https://practiceexcelonline.com/functions/unique) ### **Timed tests** - [Excel test for data analysts](https://practiceexcelonline.com/tests/excel-test-for-data-analysts) · 20 questions in 30 minutes - [Excel test for admin jobs](https://practiceexcelonline.com/tests/excel-test-for-admin-jobs) · 20 questions in 30 minutes - [Excel test for job interviews](https://practiceexcelonline.com/tests/excel-test-for-job-interview) · 20 questions in 30 minutes ## **Questions**
### How do I remove extra spaces in Excel? Use TRIM in a helper column, =TRIM(A2), and fill it down. TRIM removes spaces at the start and end and turns runs of spaces between words into one. Spaces copied from web pages are often non-breaking spaces that TRIM leaves alone; remove those with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
### How do I split a full name into first and last name? For "First Last", =LEFT(A2,FIND(" ",A2)-1) returns the first name and =MID(A2,FIND(" ",A2)+1,100) the rest. In Excel 365, TEXTBEFORE and TEXTAFTER do the same more simply. Data > Text to Columns and Flash Fill split names without formulas.
### Why does Excel not add up my numbers? Some of them are probably numbers stored as text, often from an export. SUM skips text, so the total is too low without any error. Text numbers usually sit at the left of the cell and may have a small green triangle in the corner. Convert them with VALUE, Text to Columns or the Convert to Number option.
### How do I find duplicates in Excel? Put =COUNTIF($A$2:$A$100,A2) in a helper column and fill it down; any count above 1 is a duplicate. Conditional formatting can highlight duplicate values, and Data > Remove Duplicates deletes them, so work on a copy.
### Should I clean data with formulas or by hand? With formulas, in helper columns next to the original. They are faster on more than a few rows, they show exactly what was changed, and they work again on next month's export. Paste the results as values once you are happy with them.
## **More guides** - [**How to create a drop-down list in Excel, and make it update itself**Create a drop-down list in Excel from typed options, a range or another sheet, keep it up to date, make one list depend on another and write custom validation rules.](https://practiceexcelonline.com/learn/drop-down-list-excel) - [**VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use**How VLOOKUP, XLOOKUP and INDEX with MATCH work, where each one breaks, and which to use in a test, an old workbook or a sheet you are building today.](https://practiceexcelonline.com/learn/vlookup-vs-xlookup-vs-index-match) - [**How to merge cells in Excel, and what to use instead**Merge & Center and its three siblings, why merged cells break sorting and filtering, Center Across Selection, unmerging without gaps and combining text with a formula.](https://practiceexcelonline.com/learn/merge-cells-excel) - [**Dates in Excel: how they work and the formulas you need**Why Excel stores dates as numbers, how to add days and months, find month-ends and working days, work out ages, and fix dates that arrive as text.](https://practiceexcelonline.com/learn/excel-dates) - [**Excel for job interviews: what gets tested and how to prepare**What employer Excel tests ask, the formulas that come up again and again, how the timer changes your approach, and a two-week plan to get ready.](https://practiceexcelonline.com/learn/excel-for-job-interviews) - [**Conditional formatting in Excel: highlight cells and rows with rules and formulas**How conditional formatting works, the built-in rules worth knowing, and the formulas that highlight whole rows, overdue dates, duplicates and anything a menu can't do.](https://practiceexcelonline.com/learn/conditional-formatting-excel) ```json {"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#webpage","dateModified":"2026-10-29","datePublished":"2026-10-29","description":"Fix messy exports with formulas. Remove extra spaces, fix capitals, split names and codes, join columns, find duplicates and turn text into real numbers.","name":"How to clean data in Excel with formulas","url":"https://practiceexcelonline.com/learn/clean-data-in-excel","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"mainEntity":[{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/1"},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/2"},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/3"},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/4"},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/5"}],"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/learn/clean-data-in-excel"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel guides","item":"https://practiceexcelonline.com/learn","position":2},{"@type":"ListItem","name":"How to clean data in Excel with formulas","position":3}]},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#article","@type":"Article","dateModified":"2026-10-29","datePublished":"2026-10-29","description":"Fix messy exports with formulas. Remove extra spaces, fix capitals, split names and codes, join columns, find duplicates and turn text into real numbers.","headline":"How to clean data in Excel with formulas","inLanguage":"en-US","thumbnailUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_How%2Bto%2Bclean%2Bdata%2Bin%2BExcel%2Bwith%2Bformulas%2Cdescription_~Rml4IG1lc3N5IGV4cG9ydHMgd2l0aCBmb3JtdWxhcy4gUmVtb3ZlIGV4dHJhIHNwYWNlcywgZml4IGNhcGl0YWxzLCBzcGxpdCBuYW1lcyBhbmQgY29kZXMsIGpvaW4gY29sdW1ucywgZmluZCBkdXBsaWNhdGVzIGFuZCB0dXJuIHRleHQgaW50byByZWFsIG51bWJlcnMu%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9jbGVhbi1kYXRhLWluLWV4Y2VsIg%2Cs_yVtqrOCs8fBDIMnn.png","author":{"@id":"https://practiceexcelonline.com/#/schema/person/1"},"image":{"@id":"https://practiceexcelonline.com/#/schema/image/1"},"isPartOf":{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#webpage"},"mainEntityOfPage":{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#webpage"},"publisher":{"@id":"https://practiceexcelonline.com/#identity"}},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"How do I remove extra spaces in Excel?","acceptedAnswer":{"@type":"Answer","text":"Use TRIM in a helper column, =TRIM(A2), and fill it down. TRIM removes spaces at the start and end and turns runs of spaces between words into one. Spaces copied from web pages are often non-breaking spaces that TRIM leaves alone; remove those with =TRIM(SUBSTITUTE(A2,CHAR(160),\" \"))."}},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I split a full name into first and last name?","acceptedAnswer":{"@type":"Answer","text":"For \"First Last\", =LEFT(A2,FIND(\" \",A2)-1) returns the first name and =MID(A2,FIND(\" \",A2)+1,100) the rest. In Excel 365, TEXTBEFORE and TEXTAFTER do the same more simply. Data > Text to Columns and Flash Fill split names without formulas."}},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"Why does Excel not add up my numbers?","acceptedAnswer":{"@type":"Answer","text":"Some of them are probably numbers stored as text, often from an export. SUM skips text, so the total is too low without any error. Text numbers usually sit at the left of the cell and may have a small green triangle in the corner. Convert them with VALUE, Text to Columns or the Convert to Number option."}},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/4","@type":"Question","inLanguage":"en-US","name":"How do I find duplicates in Excel?","acceptedAnswer":{"@type":"Answer","text":"Put =COUNTIF($A$2:$A$100,A2) in a helper column and fill it down; any count above 1 is a duplicate. Conditional formatting can highlight duplicate values, and Data > Remove Duplicates deletes them, so work on a copy."}},{"@id":"https://practiceexcelonline.com/learn/clean-data-in-excel#/schema/question/5","@type":"Question","inLanguage":"en-US","name":"Should I clean data with formulas or by hand?","acceptedAnswer":{"@type":"Answer","text":"With formulas, in helper columns next to the original. They are faster on more than a few rows, they show exactly what was changed, and they work again on next month's export. Paste the results as values once you are happy with them."}},{"@id":"https://practiceexcelonline.com/#/schema/person/1","@type":"Person","description":"I'm a software developer, and I built PracticeExcel to help others get comfortable with Excel, and the best way to get there is to work through the formulas yourself.","jobTitle":"founder of PracticeExcel","name":"Nicolaj Gajhede","url":"https://practiceexcelonline.com/about#nicolaj-gajhede","image":{"@id":"https://practiceexcelonline.com/#/schema/image/2"},"sameAs":["https://www.linkedin.com/in/nicolaj-gajhede/"]},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_How%2Bto%2Bclean%2Bdata%2Bin%2BExcel%2Bwith%2Bformulas%2Cdescription_~Rml4IG1lc3N5IGV4cG9ydHMgd2l0aCBmb3JtdWxhcy4gUmVtb3ZlIGV4dHJhIHNwYWNlcywgZml4IGNhcGl0YWxzLCBzcGxpdCBuYW1lcyBhbmQgY29kZXMsIGpvaW4gY29sdW1ucywgZmluZCBkdXBsaWNhdGVzIGFuZCB0dXJuIHRleHQgaW50byByZWFsIG51bWJlcnMu%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9jbGVhbi1kYXRhLWluLWV4Y2VsIg%2Cs_yVtqrOCs8fBDIMnn.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_How%2Bto%2Bclean%2Bdata%2Bin%2BExcel%2Bwith%2Bformulas%2Cdescription_~Rml4IG1lc3N5IGV4cG9ydHMgd2l0aCBmb3JtdWxhcy4gUmVtb3ZlIGV4dHJhIHNwYWNlcywgZml4IGNhcGl0YWxzLCBzcGxpdCBuYW1lcyBhbmQgY29kZXMsIGpvaW4gY29sdW1ucywgZmluZCBkdXBsaWNhdGVzIGFuZCB0dXJuIHRleHQgaW50byByZWFsIG51bWJlcnMu%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9jbGVhbi1kYXRhLWluLWV4Y2VsIg%2Cs_yVtqrOCs8fBDIMnn.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/2","@type":"ImageObject","contentUrl":"https://practiceexcelonline.com/authors/nicolaj-gajhede.webp","inLanguage":"en-US","url":"https://practiceexcelonline.com/authors/nicolaj-gajhede.webp"}]} ``` ## Sitemap See the full [sitemap](https://practiceexcelonline.com/sitemap.md) for all pages.