Excel公式：批量删除空格与小写字母前的所有句点

Got it, let's tackle this data cleaning problem head-on! You need to batch remove dots that appear right before a space followed by a lowercase letter (like the '.' before 'to' in your example). Below are formula-based solutions tailored for common spreadsheet tools:

Excel (365/2021+) #

Use the REGEXREPLACE function, which lets you leverage regular expressions for flexible text manipulation:

=REGEXREPLACE(A1, "\. ([a-z])", " $1")

Regex Pattern Breakdown: #

• \.: Matches a literal dot (we escape it with a backslash because '.' acts as a wildcard in regex)
• : Targets the space immediately after the dot
• ([a-z]): Captures any lowercase letter that follows the space (the parentheses let us reuse this character in the replacement)
• The replacement $1 keeps the space and captured lowercase letter, dropping the unwanted dot entirely.

Google Sheets #

Google Sheets supports the same REGEXREPLACE function, so the formula is identical:

=REGEXREPLACE(A1, "\. ([a-z])", " $1")

Just drag the formula down your column to apply it to all rows of data in one go.

Example Walkthrough: #

If your original cell content is:

"The dataset. contains 120 entries. to be processed."

The formula will convert it to:

"The dataset contains 120 entries to be processed."

For Older Excel Versions (Pre-365) #

If you don’t have access to REGEXREPLACE, you can use a nested SUBSTITUTE approach (it’s longer but functional). You’ll need to repeat SUBSTITUTE for every lowercase letter:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, ". a", " a"), ". b", " b"), ". c", " c"), ". d", " d"), ". e", " e"), ". f", " f"), ". g", " g"), ". h", " h"), ". i", " i"), ". j", " j"), ". k", " k"), ". l", " l"), ". m", " m"), ". n", " n"), ". o", " o"), ". p", " p"), ". q", " q"), ". r", " r"), ". s", " s"), ". t", " t"), ". u", " u"), ". v", " v"), ". w", " w"), ". x", " x"), ". y", " y"), ". z", " z")

Note: This is a brute-force workaround—stick with the regex solution if you have the option.

内容的提问来源于stack exchange，提问作者Rosemary