To find a word repeated inside one Excel cell, put the text in A2 and use the formula below in another cell; it returns the repeated words. To find duplicate entries across cells, select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The first method checks individual words within text; the second highlights cells whose full contents match.
What You’ll Need
- Excel for Microsoft 365 on Windows for the formula that splits text into words. The functions used in that formula are also available in Excel 2024, but not in older perpetual versions such as Excel 2019.
- A worksheet with the text or entries you want to check. For the formula method, the example assumes one passage is in cell A2 and the result will go in B2.
- For duplicate-value highlighting, a selected range of cells. A header row can be excluded so Excel checks only the data.
You do not need an add-in or a macro. If your Excel uses semicolons rather than commas between formula arguments, replace the commas that separate arguments with semicolons when entering a formula.
Step-by-Step
- Decide what counts as a duplicate. A repeated word inside one sentence is different from two cells that contain the same complete value. For example, the word “review” can appear twice in a paragraph even though no other cell contains that paragraph. Use the formula below for repeated words in text, and use Conditional Formatting for repeated cell values. If each cell contains just one word, highlighting duplicate values across the range is usually the quickest option.
- Enter or locate the text you want to check. For a single passage, place it in A2. Keep the passage in one cell rather than splitting it across columns. The formula in the next step ignores letter case, so “Review” and “review” count as the same word. It separates words at ordinary spaces; punctuation attached to a word is not removed automatically.
- Enter the repeated-word formula. With the passage in A2 and the output cell B2, enter this formula in B2. It returns each repeated word once, separated by commas. For example, if A2 contains “The report needs review because the report is late and the notes need review,” the result is “the, report, review.”
- Read the result and check the source. A word appearing three or more times is listed once, not once for every repeat. The formula identifies which words repeat but does not mark their exact positions in the original sentence. Use Find to locate a listed word in the source cell: press Ctrl+F on Windows, enter the word, and use Find Next. On a Mac, use Command+F.
- Copy the formula down for multiple passages. If each row contains a separate passage in column A, fill B2 down alongside the data. Select B2 and drag its fill handle, or copy and paste it into the other result cells. Excel will adjust A2 to A3, A4, and so on. Confirm that each result corresponds to the passage on the same row.
- Highlight duplicate entries across a range, if that is what you need. Select the data cells, such as A2:A100, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a format, and click OK. Excel highlights cells with values that appear more than once in the selected range. This checks the whole cell contents, not individual words inside sentences.
- Optionally label duplicate cells with a formula. If you want a text label rather than colored formatting, use a helper column. With entries in A2:A100 and the label formula in B2, enter the formula below and fill it down. If A2 contains a value that occurs at least twice in the range, B2 displays “Duplicate”; otherwise it displays “Unique.” Blank cells are left blank.
- Review the flagged results before editing. Read the source text or entries and decide whether the repeated word or value is actually an error. Repetition may be intentional, and two identical cell values may represent legitimate records. Save a copy before making bulk changes, especially if you plan to delete rows or replace text.
With the passage in A2, enter this formula in B2 to list repeated words:
=LET(words,TEXTSPLIT(LOWER(TRIM(A2))," "),uniqueWords,UNIQUE(words),TEXTJOIN(", ",TRUE,FILTER(uniqueWords,(uniqueWords<>"")*(COUNTIF(words,uniqueWords)>1),"")))
With entries in A2:A100 and the label formula starting in B2, use this formula and fill it down:
=IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique"))
The first formula requires Microsoft 365 or Excel 2024 because it uses TEXTSPLIT. It trims extra spaces at the beginning and end, converts text to lowercase, splits it at spaces, and returns words whose counts exceed one. It does not remove punctuation, so “report” and “report,” are treated as different tokens. The second formula counts matching full cell values in the fixed range. COUNTIF is not case-sensitive, so “North” and “north” count as a match.
Common Mistakes
- Expecting Conditional Formatting to find words inside sentences. Duplicate Values compares complete cell contents. If A2 says “review the draft” and A3 says “review the report,” neither cell is a duplicate of the other, even though both contain “review.” Use the repeated-word formula for text within a cell.
- Including punctuation without considering its effect. The formula splits on spaces but leaves punctuation attached. “urgent” and “urgent,” will not match. If punctuation varies, make a cleaned copy of the text first, or standardize punctuation before checking. Avoid removing punctuation blindly if it matters in your material, such as apostrophes in names.
- Assuming the formula treats every kind of spacing as a word boundary. It uses a regular space as the separator. Tabs, line breaks, nonbreaking spaces, and some pasted web text can behave differently. Replace unusual spacing with ordinary spaces if words appear to be missed.
- Counting a repeated word as an error automatically. Common words such as “and,” “the,” or “to” often appear several times in normal writing. The formula lists every repeated word, including those. Review the context and decide whether the repetition is meaningful rather than deleting every match.
- Using the wrong range in a duplicate check. If the selected range includes a title or header, Excel may highlight it if its text appears elsewhere. If a helper formula checks only A2:A100, entries beyond row 100 are not counted. Match the range to the actual data and adjust it when new rows are added.
- Deleting duplicates before checking which record to keep. Duplicate-looking records may differ in another column or may be separate legitimate transactions. Conditional Formatting only flags matches; it does not decide which row is safe to remove. Compare related fields before deleting anything.
Tips
- Use a helper column to keep the original text intact. Put the result formula beside the source data rather than overwriting it. This makes it easier to audit the result, correct the source, or remove the helper column later.
- Filter a labeled list to focus on matches. After filling the Duplicate/Unique formula down, turn on filters with Data > Filter, then filter the helper column to show Duplicate. This is useful when a long list has only a few repeated entries.
- Check the intended unit of comparison. For a list of single words, duplicate-value highlighting is simple and visible. For sentences, the formula checks tokens within each individual cell; it does not compare words across all the cells in the range. Pick the method that reflects the question you are asking.
- Test the formula on a small sample first. Try one sentence with a known repeat and one without. This confirms that spaces and punctuation in your actual data behave as expected before you fill the formula through a large sheet.
- Keep formula outputs current. The result recalculates when the source cell changes. Conditional Formatting also updates when cell values change, but if you add rows outside the selected range, extend the rule or apply it to a larger range.
- Use Excel’s search when you need positions. The formula gives a list of repeated terms, not a count or an in-text highlight. Ctrl+F on Windows or Command+F on Mac can help locate each term in the source. Search may find the same letters within a longer word, so inspect each match before changing it.
Troubleshooting
- You see #NAME? in the repeated-word formula. Your Excel version may not support TEXTSPLIT, LET, or another dynamic-array function in the formula. Use Microsoft 365 or Excel 2024, or use the Conditional Formatting method for duplicates in a list of individual words. Check that the function names have not been changed by a translation or typing error.
- The formula returns a blank when you expected a duplicate. Check that the same word is separated by ordinary spaces and has identical punctuation. “note” and “note.” are different to this formula. Also check for hidden line breaks, tabs, or nonbreaking spaces pasted from another source; replacing them with normal spaces can resolve the mismatch.
- The formula returns a word you do not consider a duplicate. The formula ignores case but does not ignore punctuation or distinguish meaningful forms of a word. Review the source cell. If two tokens are genuinely different in context, the result is a match according to the formula’s simple text rules, not necessarily an editorial mistake.
- No cells are highlighted. Make sure you selected the cells containing the data before opening the Duplicate Values rule. Confirm that the entries are exact matches, and check whether a filter or fill color makes the chosen highlight hard to see. Open Home > Conditional Formatting > Manage Rules to review the rule’s “Applies to” range.
- The helper formula shows “Unique” for a match. Confirm that the repeated value falls within A2:A100 and that the formula’s fixed range matches your data. If you put entries in a different column, update both the range and the cell reference. Also check for extra spaces, which make values look alike but compare differently.
- Excel rejects a formula with commas. Some regional settings require semicolons as argument separators. Replace argument-separating commas with semicolons, while keeping commas that are inside quoted text if they are part of the displayed output. If the formula still fails, check the opening and closing parentheses and straight quotation marks.
Frequently Asked Questions
Can Excel find a repeated word inside one cell?
Yes. In Microsoft 365 or Excel 2024, use the TEXTSPLIT formula above to list words repeated within a cell. It separates at spaces and treats capitalization as irrelevant, but punctuation remains part of each token.
Can Conditional Formatting find duplicate words in different sentences?
No. The Duplicate Values rule compares whole cell values. It will not flag two different sentences just because both contain a shared word. Use a word-counting approach suited to the range if you need to compare words across many passages.
Does the formula count uppercase and lowercase words separately?
No. It converts the source to lowercase before splitting it, so “Draft” and “draft” are treated as the same word. The helper COUNTIF formula is also case-insensitive.
Will the formula work in Excel 2019?
The repeated-word formula uses TEXTSPLIT and other dynamic-array functions that require Microsoft 365 or Excel 2024. Excel 2019 can still highlight duplicate cell values with Conditional Formatting, or label them with the COUNTIF helper formula.
Can I remove the duplicates after finding them?
You can edit repeated words or delete duplicate rows, but check the context first. The formula only identifies repeated tokens, and highlighting only identifies matching cell contents; neither determines which occurrence or record should be kept.
Does this work in Excel for Mac or Excel for the web?
The repeated-word formula works in supported Microsoft 365 versions on Mac and the web. The Conditional Formatting rule is also available, though ribbon layout can vary slightly. On Mac, use Command+F instead of Ctrl+F to search for a word.
Related
- How to Enable Word Wrap in Excel and Adjust Cell Text
- How to Enable Wrap Text in Excel and Adjust Row Height
- Excel Trust Center: How to Open and Configure Settings
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Remove Section Breaks in Word Without Losing FormattingTo remove a section break in Microsoft Word, show formatting marks with Home > ¶, select the section…
- How to Copy a Table in Excel Without Losing FormattingTo copy a table in Excel for Microsoft 365 on Windows, select the whole table, press Ctrl+C, click…
- Excel Consolidate Lists With Duplicates: Keep or Remove?To combine lists and keep repeated items in Excel for Microsoft 365 on Windows, enter =VSTACK(A2:A5,B2:B4) in an…
- How to Insert a Row in Excel After Any Specific RowTo insert a blank row after a specific row in Excel, select the row number directly below it,…
- How to Add Page Numbers 1 of 3 in Word Step by StepTo show numbering such as 1 of 3 in Microsoft Word, choose Insert > Page Number and select…
- Excel Shortcut to Select an Entire Column on WindowsIn Excel for Microsoft 365 on Windows, press Ctrl+Space to select the entire column containing the active cell.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.