# Duplicates and data cleanup formulas in Excel and Google Sheets

> Find, flag, count and remove duplicates, and fix numbers stored as text. 6 answers, each checked by running it.

Source: https://spreadsheetpractice.com/formulas/topic/cleanup · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 29, 2026

## Answers

- [How to find duplicates in a column in Excel](https://spreadsheetpractice.com/formulas/find-duplicates-in-a-column): Count each value in the whole column: =IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate",""). Any value that appears more than once is flagged. `=IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate","")`
- [How to compare two lists and find matches in Excel](https://spreadsheetpractice.com/formulas/compare-two-lists-for-matches): Check each item against the other list with COUNTIF: =IF(COUNTIF(other_list,A2),"In both","Only in list 1"). `=IF(COUNTIF($D$2:$D$5,A2),"Registered","Not yet")`
- [How to convert numbers stored as text to numbers in Excel](https://spreadsheetpractice.com/formulas/convert-text-to-numbers): Use =VALUE(A2), or multiply by 1. The result is a real number that sums, sorts and looks up correctly. `=VALUE(A2)`
- [How to remove a specific character from text in Excel](https://spreadsheetpractice.com/formulas/remove-a-character-from-text): Replace it with nothing: =SUBSTITUTE(A2,"-",""). SUBSTITUTE swaps every copy of the character. `=SUBSTITUTE(A2,"-","")`
- [How to mark the first occurrence of each value in Excel](https://spreadsheetpractice.com/formulas/mark-first-occurrence-of-duplicates): Count from the top of the list down to the current row: =IF(COUNTIF($A$2:A2,A2)=1,"First","Repeat"). `=IF(COUNTIF($A$2:A2,A2)=1,"First","Repeat")`
- [How to add leading zeros to numbers in Excel](https://spreadsheetpractice.com/formulas/add-leading-zeros): Format the number as text with a fixed number of digits: =TEXT(A2,"00000"). `=TEXT(A2,"00000")`

More topics: [Counting formulas](https://spreadsheetpractice.com/formulas/topic/count) · [Sum formulas](https://spreadsheetpractice.com/formulas/topic/sum) · [Lookup formulas](https://spreadsheetpractice.com/formulas/topic/lookup) · [IF and logic formulas](https://spreadsheetpractice.com/formulas/topic/logic) · [Text formulas](https://spreadsheetpractice.com/formulas/topic/text) · [Date formulas](https://spreadsheetpractice.com/formulas/topic/date) · [Dynamic array formulas](https://spreadsheetpractice.com/formulas/topic/dynamic) · [Rounding and math formulas](https://spreadsheetpractice.com/formulas/topic/math) · [Percentage formulas](https://spreadsheetpractice.com/formulas/topic/percent)

Every answer: [Formula answers](https://spreadsheetpractice.com/formulas). Formulas used: `IF`, `COUNTIF`, `VALUE`, `SUBSTITUTE`, `TEXT`

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.