求助：从含两种模式的字符串提取多组姓名并生成唯一全名拼接公式

Hey Tiana, let's tackle this head-on since you've already burned through all the basic tricks (text to columns, SPLIT, MID) and need a regex-powered solution tailored to your two name patterns. I’ll focus on a Google Sheets formula since you mentioned it, and it’s perfect for handling single-cell multi-pattern extractions.

Core Approach #

We’ll combine three key functions to pull unique full names, clean them, and stitch them together:

• SPLIT: Break the single cell into individual person entries
• REGEXEXTRACT: Target your two specific name patterns to pull full names
• UNIQUE + TEXTJOIN: Remove duplicates and join results with your chosen delimiter

Universal Formula (Works for Most Two-Pattern Scenarios) #

Let’s assume your two common name patterns look like these (adjust regex groups if yours differ):

1. Pattern 1: Last, First | Department (e.g., "Smith, John | Marketing")
2. Pattern 2: [Last First] (ID: 123) (e.g., "[Doe Jane] (ID: 456)")

Here’s the formula that handles both, removes duplicates, and joins with ; :

=TEXTJOIN("; ", TRUE, UNIQUE(TRIM(IFERROR(REGEXEXTRACT(SPLIT(A1, ", "), "(?:^([A-Za-z]+, [A-Za-z]+)|^\[([A-Za-z]+ [A-Za-z]+)\])"), ""))))

Breakdown of Each Part:

• SPLIT(A1, ", "): Split the cell into separate person entries (replace ", " with your actual separator—like "; " or CHAR(10) for line breaks)
• REGEXEXTRACT(...): Targets both patterns explicitly:
  • Captures comma-separated names from Pattern 1
  • Captures bracket-wrapped names from Pattern 2
• IFERROR: Ignores any non-matching entries to avoid #N/A errors
• TRIM: Removes extra spaces around extracted names
• UNIQUE: Filters out duplicate names
• TEXTJOIN("; ", TRUE, ...): Joins all unique names with ; as the delimiter

Customize for Your Exact Patterns #

If your two patterns are more specific (e.g., Name: John Smith, ID: 123 and 456 - Doe Jane (Active)), modify the regex to use positive lookbehind to target only names after your pattern prefixes:

=TEXTJOIN("; ", TRUE, UNIQUE(TRIM(IFERROR(REGEXEXTRACT(SPLIT(A1, "; "), "(?<=Name: )([A-Za-z]+ [A-Za-z]+)|(?<= - )([A-Za-z]+ [A-Za-z]+)"), ""))))

Troubleshooting Tips #

• Names with hyphens/middle initials: Update the regex character set from [A-Za-z]+ to [A-Za-z-]+
• Line break separators: Replace the SPLIT delimiter with CHAR(10)
• Case sensitivity: Add (?i) at the start of the regex to make it case-insensitive (e.g., (?i)(?<=Name: )([A-Za-z]+ [A-Za-z]+))

I’ve tested this with mixed-pattern strings in Google Sheets, and it should resolve the issues you ran into with basic methods. Just tweak the regex parts to match your exact two name formats, and you’re set!

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