如何在Excel中通过字符串文本提取实现批量商品分类

Absolutely! You can tackle this 670k-item categorization task entirely with formulas—no manual slog required. Let’s cover both single-keyword and multi-keyword scenarios since you mentioned "specified keywords" (I’m guessing you might have more than one!).

Single Keyword Scenario #

If you only need to check for one keyword (e.g., "Example"), use this formula in cell F2 (then drag/fill down to all 670k rows):

=IF(ISNUMBER(SEARCH("Example", C2)), "Example", "")

• SEARCH("Example", C2) checks if the keyword exists in the title (it’s case-insensitive; use FIND instead if you need case-sensitive matching).
• ISNUMBER converts the search result (a position number if found, #VALUE! if not) into a TRUE/FALSE value.
• The IF statement outputs the keyword if found, or a blank cell if not.

Multi-Keyword Scenario #

If you have multiple keywords to check (e.g., "Example", "Widget", "Gadget"), a nested IF would work but gets messy fast. Instead, use this clean LOOKUP formula:

=IFERROR(LOOKUP(2, 1/ISNUMBER(SEARCH({"Example","Widget","Gadget"}, C2)), {"Example","Widget","Gadget"}), "")

Here’s how it works:

1. SEARCH({"Example","Widget","Gadget"}, C2) runs a search for each keyword in the title, returning position numbers for matches or #VALUE! for non-matches.
2. ISNUMBER converts those results to TRUE/FALSE, then 1/ turns TRUE into 1 and FALSE into #DIV/0!.
3. LOOKUP(2, ...) looks for a value greater than all valid 1s (since 2 is larger), which makes it return the last matching keyword. If you want the first matching keyword instead, use XLOOKUP with match_mode=0 in newer Excel versions.
4. IFERROR wraps the whole thing to return a blank cell if no keywords are found (instead of #N/A).

Pro Tips #

• Special Characters: If your keywords include wildcards like * or ?, add a tilde (~) before them in the formula (e.g., SEARCH("~*Special", C2)) to avoid Excel treating them as pattern matchers.
• Batch Application: Once you enter the formula in F2, double-click the small square at the bottom-right of the cell (the fill handle)—Excel will auto-fill it down to the last row with data in column C, even for 670k rows.
• Performance: For large datasets, SEARCH is efficient enough, but if you notice lag, consider converting your data to an Excel Table (Ctrl+T) first—tables optimize formula calculations for big datasets.

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