如何在Excel中自动提取文件名至第二个下划线的短版本

Hey there! I totally get the frustration of fumbling with Text to Columns or manually adjusting LEFT function parameters—let's fix this with dynamic formulas that work automatically, no guesswork required.

Solution 1: Universal Formula (Works for All Excel Versions) #

If you're using an older Excel version without the latest text functions, this nested FIND + LEFT combo will dynamically locate the second underscore and pull everything before it:

=LEFT(A1, FIND("_", A1, FIND("_", A1)+1)-1)

Breakdown:

• FIND("_", A1) pinpoints the position of the first underscore.
• FIND("_", A1, FIND("_", A1)+1) starts searching for the second underscore right after the first one, so it skips the initial match.
• Subtract 1 from that second underscore position to exclude the underscore itself, then LEFT grabs all characters up to that clean cutoff point.

Solution 2: Simplified Formula (Excel 365/2021+) #

If you have access to modern Excel, the TEXTBEFORE function makes this dead simple—it’s built exactly for this kind of targeted extraction:

=TEXTBEFORE(A1, "_", 2)

Breakdown:

• The third parameter 2 tells Excel to extract all text before the second occurrence of the underscore. No nested functions, no math—just clean, intuitive syntax.

Automate the Entire Column #

To apply this to a full column without manual drag-and-drop:

• For older Excel: Enter the formula in the first cell (e.g., B1), then click the bottom-right corner of the cell and drag down to fill the rest of the column.
• For Excel 365: Use =TEXTBEFORE(A:A, "_", 2)—the formula will automatically spill to the entire column instantly, no dragging needed.

Example: If cell A1 contains Project_Q4_2024_Final.docx, both formulas will return Project_Q4—no extra underscores, no manual parameter tweaks, every time.

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