Duplicates and data cleanup formulas
Find, flag, count and remove duplicates, and fix numbers stored as text. 6 answers, each checked by running it, with a live example and an exercise.
How to find duplicates in a column in Excel
=IF(COUNTIF($A$2:$A$10,A2)>1,"Duplicate","")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.
How to compare two lists and find matches in Excel
=IF(COUNTIF($D$2:$D$5,A2),"Registered","Not yet")Check each item against the other list with COUNTIF: =IF(COUNTIF(other_list,A2),"In both","Only in list 1").
How to convert numbers stored as text to numbers in Excel
=VALUE(A2)Use =VALUE(A2), or multiply by 1. The result is a real number that sums, sorts and looks up correctly.
How to remove a specific character from text in Excel
=SUBSTITUTE(A2,"-","")Replace it with nothing: =SUBSTITUTE(A2,"-",""). SUBSTITUTE swaps every copy of the character.
How to mark the first occurrence of each value in Excel
=IF(COUNTIF($A$2:A2,A2)=1,"First","Repeat")Count from the top of the list down to the current row: =IF(COUNTIF($A$2:A2,A2)=1,"First","Repeat").
How to add leading zeros to numbers in Excel
=TEXT(A2,"00000")Format the number as text with a fixed number of digits: =TEXT(A2,"00000").