文本挖掘需求：查找指定前缀开头词汇并生成标记值

Got it, let's tackle this efficiently—50k rows means we need accurate, performant solutions. Below are options for Excel, Power Query, and Python, depending on your workflow:

Excel Worksheet Formula (Quick & Direct) #

If you're working directly in Excel, use the REGEXMATCH function (available in Excel 365/2021+) for clean, reliable matching. It handles word boundaries and case insensitivity perfectly:

=IF(REGEXMATCH(A1, "\bsurv\w*", "i"), 1, 0)

• Breakdown:
  • \b: Ensures we match a whole word (avoids partial matches like "unsurvive")
  • surv: The prefix we're targeting
  • \w*: Matches any number of letters after the prefix
  • "i": Makes the match case-insensitive (so "Survive" or "SURVIVOR" are caught too)

Drag this formula down your column to apply it to all 50k rows.

For older Excel versions without REGEXMATCH, use this array formula (press Ctrl+Shift+Enter to activate):

=IF(SUM(--(ISNUMBER(SEARCH(" surv", " "&A1&" "))*LEFT(MID(" "&A1&" ",SEARCH(" surv", " "&A1&" ")+1,99),4)="surv")),1,0)

This adds spaces around the cell text to mimic word boundaries, then checks for " surv" (with a leading space) and verifies the first 4 characters are "surv".

Power Query (Bulk Processing) #

Power Query is great for handling large datasets without dragging formulas. Here's how to set it up:

1. Select your data column, go to Data > From Table/Range (check "My table has headers" if applicable)
2. In the Power Query Editor, go to Add Column > Custom Column
3. Paste this formula (replace [TextColumn] with your actual column name):

= if Text.RegexMatch([TextColumn], "\bsurv\w*", [IgnoreCase=true]) then 1 else 0

4. Click OK, then go to Home > Close & Load to export the results back to Excel.

Python (Automated/Advanced Workflows) #

If you're comfortable with Python, pandas + regex will process 50k rows in seconds. Here's a complete script:

import pandas as pd
import re

# Load your Excel file (replace with your file path)
df = pd.read_excel("your_input_file.xlsx")

# Regex pattern: matches whole words starting with "surv" (case-insensitive)
surv_pattern = re.compile(r'\bsurv\w*', re.IGNORECASE)

# Create the flag column
df["Has_Surv_Prefix"] = df["YourTextColumn"].apply(
    lambda text: 1 if surv_pattern.search(str(text)) else 0
)

# Save the results to a new Excel file
df.to_excel("flagged_results.xlsx", index=False)

• This handles missing values automatically (converts them to string before checking)
• The regex ensures we only match words starting with "surv", not partial matches inside other words.

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