Duplicates and data cleanup formulas
Find, flag, count and remove duplicates, and fix numbers stored as text. 40 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").
How to remove non-breaking spaces in Excel
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))Turn them into normal spaces, then trim: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). TRIM on its own leaves non-breaking spaces in place.
How to remove line breaks from cells in Excel
=TRIM(SUBSTITUTE(A2,CHAR(10)," "))Replace CHAR(10), the line break, with a space and trim: =TRIM(SUBSTITUTE(A2,CHAR(10)," ")).
How to replace line breaks with commas in Excel
=SUBSTITUTE(A2,CHAR(10),", ")Use =SUBSTITUTE(A2,CHAR(10),", "). CHAR(10) is the line break inside a cell.
How to remove non-printing characters in Excel
=TRIM(CLEAN(A2))Use =TRIM(CLEAN(A2)). CLEAN removes the invisible control characters (codes 0 to 31) and TRIM removes extra spaces.
How to find cells with leading or trailing spaces in Excel
=OR(LEFT(A2)=" ",RIGHT(A2)=" ")Check the first and last characters: =OR(LEFT(A2)=" ",RIGHT(A2)=" ") returns TRUE for cells that start or end with a space.
How to fix negative numbers with a trailing minus sign in Excel
=IF(RIGHT(A2)="-",-VALUE(LEFT(A2,LEN(A2)-1)),VALUE(A2))Move the minus to the front: =IF(RIGHT(A2)="-",-VALUE(LEFT(A2,LEN(A2)-1)),VALUE(A2)).
How to remove currency symbols and convert text to numbers in Excel
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"USD",""),"$",""))Strip the symbols with SUBSTITUTE, then convert with VALUE: =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"USD",""),"$","")).
How to convert numbers with comma decimals to real numbers in Excel
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",","."))Remove the dot thousands separators, turn the comma into a dot, then convert: =VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",",".")).
How to remove parentheses, dashes and spaces from phone numbers in Excel
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")," ",""),".","")Nest one SUBSTITUTE for each character to delete. In Google Sheets, =REGEXREPLACE(A2,"\D","") removes everything that isn't a digit.
How to remove all spaces from a cell in Excel
=SUBSTITUTE(A2," ","")Replace every space with nothing: =SUBSTITUTE(A2," ",""). Unlike TRIM, it also removes the spaces between words.
How to remove duplicate values within a cell in Excel
=TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(A2,,", ")))Split the list, keep unique items and join it back: =TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(A2,,", "))).
How to standardize yes and no values in Excel
=IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check"))Match the trimmed value against lists of yes and no spellings: =IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check")).
How to standardize inconsistent text entries with a lookup table in Excel
=XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2)List each variant with its standard value, then look it up: =XLOOKUP(TRIM(A2),$D$2:$D$7,$E$2:$E$7,"Check: "&A2).
How to fill blank cells with the value above using a formula in Excel
=IF(A2="",D1,A2)In a helper column, take the value when there is one and the cell above otherwise: =IF(A2="",D1,A2), filled down.
How to replace blank cells with 0 using a formula in Excel
=IF(C2="",0,C2)Return 0 when the cell is empty and the value otherwise: =IF(C2="",0,C2).
How to remove a leading apostrophe from cells in Excel
=IF(LEFT(A2)="'",MID(A2,2,LEN(A2)),A2)When the apostrophe is a real character in the text, drop it with =IF(LEFT(A2)="'",MID(A2,2,LEN(A2)),A2).
How to convert text like 12 kg to a number in Excel
=VALUE(TEXTBEFORE(A2," "))*IF(TEXTAFTER(A2," ")="g",0.001,1)Take the number before the space and scale it by the unit: =VALUE(TEXTBEFORE(A2," "))*IF(TEXTAFTER(A2," ")="g",0.001,1) converts 500 g to 0.5 kg.
How to separate letters and numbers in a cell in Excel
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)Find the first digit and take everything before it: =LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1) returns the letters.
How to remove a trailing comma from text in Excel
=IF(RIGHT(A2)=",",LEFT(A2,LEN(A2)-1),A2)Drop the last character only when it is a comma: =IF(RIGHT(A2)=",",LEFT(A2,LEN(A2)-1),A2).
How to remove a prefix from text in Excel
=IF(LEFT(A2,LEN($E$1))=$E$1,MID(A2,LEN($E$1)+1,LEN(A2)),A2)Check for the prefix, then keep what follows it: =IF(LEFT(A2,LEN($E$1))=$E$1,MID(A2,LEN($E$1)+1,LEN(A2)),A2).
How to fix spacing in comma-separated lists in Excel
=TEXTJOIN(", ",TRUE,TRIM(TEXTSPLIT(A2,",")))Split at the commas, trim each item and join them back: =TEXTJOIN(", ",TRUE,TRIM(TEXTSPLIT(A2,","))).
How to find values with the wrong number of characters in Excel
=IF(LEN(A2)=$E$1,"OK","Check")Compare the length with the expected one: =IF(LEN(A2)=$E$1,"OK","Check") flags every value that is too short or too long.
How to convert numbers to text in Excel
=TEXT(A2,"0")Use =TEXT(A2,"0") for whole numbers, or =A2&"" to keep the number exactly as it is. Both return text.
How to remove leading zeros from text in Excel
=MID(A2,MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0),LEN(A2))Start at the first character that isn't 0: =MID(A2,MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0),LEN(A2)). It works on codes with letters, where VALUE fails.
How to check if a cell is a number or text in Excel
=IF(ISNUMBER(A2),"Number",IF(ISTEXT(A2),"Text","Blank"))Use ISNUMBER and ISTEXT: =IF(ISNUMBER(A2),"Number",IF(ISTEXT(A2),"Text","Blank")) labels each cell.
How to remove blank rows with a formula in Excel
=FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>""))Keep the rows where any column has data: =FILTER(A2:C9,(A2:A9<>"")+(B2:B9<>"")+(C2:C9<>"")).
How to compare two columns ignoring extra spaces and case in Excel
=TRIM(A2)=TRIM(B2)TRIM both sides and compare with =: =TRIM(A2)=TRIM(B2). The = comparison already ignores upper and lower case.
How to convert 1.2K and 3M text to numbers in Excel
=IF(RIGHT(A2)="K",LEFT(A2,LEN(A2)-1)*1000,IF(RIGHT(A2)="M",LEFT(A2,LEN(A2)-1)*1000000,VALUE(A2)))Check the last character and multiply: =IF(RIGHT(A2)="K",LEFT(A2,LEN(A2)-1)*1000,IF(RIGHT(A2)="M",LEFT(A2,LEN(A2)-1)*1000000,VALUE(A2))).
How to remove quotation marks from text in Excel
=SUBSTITUTE(A2,"""","")Replace the quote character with nothing: =SUBSTITUTE(A2,"""",""). The four quotes stand for one quote mark.
How to remove accents from letters in Excel
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"é","e"),"í","i"),"ë","e"),"ü","u"),"ç","c"),"è","e"),"ñ","n"),"Á","A")Swap each accented letter for its plain one with nested SUBSTITUTE, one per letter: =SUBSTITUTE(SUBSTITUTE(A2,"é","e"),"ñ","n") and so on.
How to remove special characters from text in Excel
=TRIM(LET(c,MID(A2,SEQUENCE(LEN(A2)),1),CONCAT(IF(ISNUMBER(FIND(LOWER(c),"abcdefghijklmnopqrstuvwxyz0123456789 ")),c,""))))In Excel 365, keep only letters, digits and spaces: =TRIM(LET(c,MID(A2,SEQUENCE(LEN(A2)),1),CONCAT(IF(ISNUMBER(FIND(LOWER(c),"abcdefghijklmnopqrstuvwxyz0123456789 ")),c,"")))).
How to remove HTML tags from text in Google Sheets
=TRIM(SUBSTITUTE(REGEXREPLACE(A2,"<[^>]+>"," "),"&","&"))Replace every tag with a space using REGEXREPLACE, then tidy up: =TRIM(SUBSTITUTE(REGEXREPLACE(A2,"<[^>]+>"," "),"&","&")).
How to format messy phone numbers in Google Sheets
=REGEXREPLACE(REGEXREPLACE(A2,"\D",""),"^1?(\d{3})(\d{3})(\d{4})$","($1) $2-$3")Strip everything but digits, then rebuild the format with capture groups: =REGEXREPLACE(REGEXREPLACE(A2,"\D",""),"^1?(\d{3})(\d{3})(\d{4})$","($1) $2-$3").
How to extract a date from text in Google Sheets
=DATEVALUE(REGEXEXTRACT(A2,"\d{1,2}/\d{1,2}/\d{4}"))Find the date with REGEXEXTRACT and convert it: =DATEVALUE(REGEXEXTRACT(A2,"\d{1,2}/\d{1,2}/\d{4}")), formatted as a date.