正则表达式优化：如何精准匹配backordered的各类间隔/大小写变体？

Got it, let's sort out this regex issue. Your original formula =REGEXMATCH("Back ordered", "^[a-z A-Z]*$") is way too broad—it only checks if the input is made of letters and spaces, which is why random text like "Someabcd" gets flagged as a match. We need a regex that specifically targets the sequence of characters in "backordered", while accounting for all the variations you mentioned (mixed case, spaces between characters, hyphens, etc.).

The Updated Regex Formula #

Here's the formula you should use in Google Sheets:

=REGEXMATCH(A1, "^(?i)b\s*-?a\s*-?c\s*-?k\s*-?o\s*-?r\s*-?d\s*-?e\s*-?r\s*-?e\s*-?d$")

Breakdown of the Regex #

Let's break down how this works to make sure you understand:

• ^ and $: These anchor the regex to the start and end of the string, ensuring we match the entire input (no partial matches like "BackorderedXYZ" will slip through).
• (?i): Enables case-insensitive matching, so any combination of uppercase/lowercase letters (like BaCkOrdeRed) will work.
• b\s*-?a: Matches the letter b (case-agnostic), followed by 0 or more spaces (\s*) OR an optional hyphen (-?), then the letter a. This pattern repeats for every character in "backordered", allowing spaces or hyphens between any of the letters.

Examples of Matches & Non-Matches #

Will return TRUE: #

• BackOrdered
• BAck ordered
• B ack ordered
• Back-ordered
• backordered
• BaCkOrdeRed
• b a c k o r d e r e d
• B-a-c-k-o-r-d-e-r-e-d

Will return FALSE: #

• Someabcd (unrelated text)
• Backorder (missing the final "ed")
• Back ordered 123 (extra characters at the end)
• Orderedback (wrong character sequence)

This regex is precise enough to only target the variations of "backordered" you care about, while excluding unrelated text that your original formula was incorrectly matching.

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