Remove Duplicate Words and Extra Punctuation from Text

Free-text columns — product descriptions, delivery notes, ticket summaries, survey comments — collect small mistakes that make reports look careless: Delivered to to the warehouse, Fragile!!!, Leave with neighbour , no 12, “Urgent” with curly quotes in one row and straight ones in the next. None of them changes the meaning, but they spoil printed reports, break exact matching and inflate the count of distinct values. They are also easy to fix with a handful of regular expressions, provided the rules are careful about what they change: a blanket rule that deletes every repeated word would also turn the island Bora Bora into Bora and the company Duran Duran Ltd into something else. This page builds a small text-cleaning function for pandas that removes accidental repeated words with an allowlist for genuine ones, collapses repeated punctuation, fixes spacing around punctuation, normalises quotes and dashes, and records every cell it changes so the cleaning can be reviewed. It belongs to Cleaning Excel Data with pandas.

Prerequisites

pip install pandas openpyxl

The sample is ten delivery notes with the usual problems: repeated words, runs of exclamation and question marks, a space before a comma, curly quotes and an en dash, surrounding spaces, leading and trailing hyphens — and two legitimate repeats that must survive.

Step 1 — Repeated words, with an allowlist

import re
import pandas as pd

notes = pd.read_excel("ticket_notes.xlsx")
KEEP = {"bora bora", "that that", "ok ok", "duran duran"}          # genuine repeats

def drop_repeats(text):
    def repl(m):
        pair = f"{m.group(1)} {m.group(2)}".lower()
        return m.group(0) if pair in KEEP else m.group(1)
    return re.sub(r"\b(\w+)\s+(\1)\b", repl, text, flags=re.IGNORECASE)

for t in notes["Note"].head(7):
    print(f"{t!r:45} -> {drop_repeats(t)!r}")

The pattern \b(\w+)\s+(\1)\b finds a word followed by whitespace and the same word again, as whole words thanks to the \b boundaries — so the theatre is not touched. With IGNORECASE, The the is caught as well. Instead of replacing every match blindly, the replacement is a function that looks the pair up in an allowlist and leaves genuine repeats alone: Bora Bora, that that in "said that that was fine", which is grammatical English, and OK OK style notes are kept, while to to and was was lose their second word. Keeping the allowlist small and visible in the code is the point — anyone reviewing the cleaning can see which repeats are treated as real.

Step 2 — Punctuation, quotes, dashes and spaces

RULES = [
    (r"[“”„]", '"'), (r"[‘’]", "'"),                     # curly quotes -> straight
    (r"\s*[–—]\s*", " - "),                              # en and em dashes -> spaced hyphen
    (r"\s+([,.;:!?])", r"\1"),                           # no space before punctuation
    (r"([!?])\1+", r"\1"),                               # !!! -> !   ?? -> ?
    (r"(?<!\.)\.{2}(?!\.)", "."),                        # exactly two dots -> one (keep ... as an ellipsis)
    (r",{2,}", ","),                                     # ,, -> ,
    (r"([,;:])(?=\S)", r"\1 "),                          # space after , ; : when missing
    (r"\s{2,}", " "),                                    # runs of spaces -> one
]

def clean_text(text):
    if not isinstance(text, str):
        return text
    out = drop_repeats(text)
    for pattern, repl in RULES:
        out = re.sub(pattern, repl, out)
    return out.strip(" -–—,;:")                      # keep a final full stop

notes["Clean"] = notes["Note"].map(clean_text)
print(notes[["Note", "Clean"]].to_string(index=False))

Each rule fixes one thing, and their order matters a little. Quotes come first, so later rules see plain characters. Dashes are turned into a spaced hyphen. Spaces before punctuation are removed and a single space is added after commas, semicolons and colons where it is missing. Runs of exclamation and question marks collapse to one. Two full stops become one, but three are left alone, because an ellipsis is intentional. Repeated commas collapse, and runs of spaces become one. Finally, leading and trailing spaces, dashes, commas and colons are stripped — which is what turns - Collected from depot - into Collected from depot — while a final full stop is kept, since it ends a sentence rather than being junk. Non-text values, such as a blank cell read as NaN, are passed through untouched.

Step 3 — Log what changed

changed = notes[notes["Note"] != notes["Clean"]]
log = changed.assign(Change=[
    ", ".join(name for name, pattern in [("repeated word", r"\b(\w+)\s+\1\b"), ("punctuation", r"[!?]{2}|\.\.|,,|\s[,.]"),
                                          ("quotes/dashes", r"[“”‘’–—]"), ("spacing", r"^\s|\s$|\s{2}"),
                                          ("leading/trailing junk", r"^[-–—,;:]|[-–—,;:]$")]
              if re.search(pattern, before, flags=re.IGNORECASE))
    for before in changed["Note"]])
print(f"{len(changed)} of {len(notes)} notes changed")
print(log[["Ticket", "Note", "Clean", "Change"]].to_string(index=False))

with pd.ExcelWriter("ticket_notes_clean.xlsx") as xw:
    notes.drop(columns="Note").rename(columns={"Clean": "Note"}).to_excel(xw, sheet_name="Notes", index=False)
    log.to_excel(xw, sheet_name="Changes", index=False)

Text cleaning changes what people wrote, so it should leave a trail. Comparing the original and cleaned columns gives every row that changed, and a few quick pattern checks on the original label why — repeated word, punctuation, quotes or dashes, spacing. The cleaned notes go to the output file under the original column name, and the change log goes on its own sheet. In the sample, seven of the ten notes change; the three that do not are the two genuine repeats and OK. OK., whose full stops keep the words from counting as a repeat. A reviewer can read the log in a minute and spot a rule that went too far; if one did, the fix is an addition to the allowlist or a tighter pattern, and the cleaning is rerun from the original data.

Pin the rules down with test cases

Regular expressions are easy to get slightly wrong and easy to break later with a well-meant tweak, so the cleaning function deserves a short list of examples that must keep working. Each case is an input and the exact output expected, chosen to cover one rule — and, just as important, the cases that must not change.

CASES = [
    ("Delivered to to the warehouse", "Delivered to the warehouse"),
    ("The the box", "The box"),
    ("Holiday booking for Bora Bora", "Holiday booking for Bora Bora"),
    ("Visit the theatre", "Visit the theatre"),
    ("Wait...", "Wait..."),
    ("Call me , please", "Call me, please"),
    ("Price: £12,50", "Price: £12, 50"),
]
for given, expected in CASES:
    got = clean_text(given)
    print(f"{'ok ' if got == expected else 'BAD'} {given!r} -> {got!r}")

The ellipsis case earned its place while this page was being written: the first version of the two-dots rule had only a lookahead, \.{2}(?!\.), which happily matched the last two dots of Wait... and turned the ellipsis into two dots. Adding a lookbehind, (?<!\.), so the pair must not be preceded by a dot either, fixed it — and the case stays in the list so the bug cannot come back. The last case is there on purpose too: it shows a rule doing something unwanted. The space after a comma rule turns the European decimal £12,50 into £12, 50, because it cannot tell a decimal comma from a list separator. Writing the case down makes the decision explicit — either the rule is restricted to commas followed by a letter, ,(?=[A-Za-z]), or text columns containing prices are excluded from cleaning. The test list is where such trade-offs are recorded, and running it after every change keeps the rules honest.

Where to stop

These rules fix typing accidents; they are not a spell-checker or a grammar tool, and they should not try to be. Misspellings, inconsistent abbreviations (St and Street) and casing are separate jobs with their own risks — see Fix the Capitalisation of Names with Exceptions in pandas. Some fields should not be touched at all: product codes, addresses used for delivery, and anything quoted verbatim in a legal or complaint context. Apply the function to the columns where tidy text helps a reader, keep the original data, and treat the change log as part of the output.

Common pitfalls

• Deleting every repeated word. Use an allowlist for genuine repeats like Bora Bora or that that.
• Matching inside words. Use \b boundaries so the theatre is not changed.
• Turning an ellipsis into a full stop. Collapse two dots, not three.
• Cleaning non-text cells. Pass through anything that is not a string.
• No record of changes. Log every changed cell with the reason.

Conclusion

To tidy free text from Excel, remove repeated words with a whole-word regular expression and a replacement function that keeps an allowlist of genuine repeats, then apply a short ordered list of rules for quotes, dashes, spacing around punctuation and repeated punctuation, and strip junk from the ends. Write the cleaned text under the original column name and log every changed cell with the reason, keeping the original data so any rule can be revised and rerun.

Frequently asked questions

How do I remove repeated words like "the the" in pandas? Use re.sub(r"\b(\w+)\s+\1\b", r"\1", text, flags=re.IGNORECASE) inside Series.map, with an allowlist for genuine repeats.

How do I replace repeated exclamation marks with one? Use the pattern ([!?])\1+ with the replacement \1.

How do I remove spaces before commas and full stops? Replace \s+([,.;:!?]) with \1.

How do I convert curly quotes to straight quotes in pandas? Replace [“”„] with " and [‘’] with ' using str.replace(..., regex=True).

Related

• Up one level: Cleaning Excel Data with pandas — cleaning workflows.
• Strip Whitespace and Normalise Text Columns with pandas — whitespace and invisible characters.
• Map Free-Text Job Titles to Standard Categories — the next step for free text.
• Fix KeyError from Spaces in Excel Column Names — the same problem in headings.