Function Problems

How to Extract Text Before the Second Underscore in Excel

Elise Williams Sep 25, 2026 871 views

Question details

The user needs a formula to extract all text appearing before the second underscore in a text string, accommodating rows with varying text lengths.

How to Extract Text Before the Second Underscore in Excel

Product: Excel
Device & OS: not provided
Scenario: Cleaning and structuring data strings (such as FIRST_LAST_TITLE_BRANCH) by isolating specific substrings based on delimiter positions.
Observed behavior: Requires a dynamic formula approach because fixed character counts cannot be used due to the varying lengths of the text in each cell.

Before you start

Ensure your text data is located in a single column and check if any cells contain fewer than two underscores, as this will determine whether you need to include error-handling in your formula.

Solution 1Recommended

Use LEFT and FIND Functions (Maximum Compatibility)

This formula is the most reliable method because it works across all versions of Excel and other spreadsheet applications.

The FIND function is used twice: first to locate the first underscore, and second to start searching immediately after that first underscore to find the second one. The LEFT function then extracts everything up to that calculated position.

Select the destination cell

Click on the empty cell where you want the extracted text to appear (e.g., cell B1 if your original text is in A1).

Enter the nested formula

Type the following formula: =LEFT(A1,FIND("_",A1,FIND("_",A1)+1)-1)

Apply and drag

Press Enter to execute the formula. Click the fill handle in the bottom-right corner of the cell and drag it down to apply the formula to the rest of your rows.

Use LEFT and FIND Functions (Maximum Compatibility)

Solution 2

Add IFERROR to Handle Missing Underscores

Use this variation if your dataset is inconsistent and some cells might only have one or zero underscores.

Solution 3

Use the TEXTBEFORE Function (Newer Versions Only)

For users with modern versions of Excel, the TEXTBEFORE function provides a much simpler and cleaner syntax.

Powerful Data Processing

Extract and Clean Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced text extraction formulas like LEFT, FIND, and IFERROR, allowing you to manipulate and clean complex data strings exactly as you would in Microsoft Excel.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your text strings.
2. Input the formula: Select an empty cell next to your target string and type =LEFT(A1,FIND("_",A1,FIND("_",A1)+1)-1).
3. Apply to multiple rows: Press Enter, then double-click the small square at the bottom-right of the cell to autofill the formula for your entire dataset.

100% compatibility with Microsoft Excel formulas and .xlsx file formatsFeatures a comprehensive library of built-in text and logic functionsLightweight application that processes large datasets smoothly on any device

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FIND formula return a #VALUE! error?

This error occurs when the text string in the target cell does not contain a second underscore. To resolve this, wrap your formula in the IFERROR function, like =IFERROR(LEFT(A1,FIND("_",A1,FIND("_",A1)+1)-1), A1), which will return the original text instead of an error.

Can I use these formulas to extract text before a different character?

Yes. You can easily adapt these formulas for any delimiter. Simply replace the underscore "_" in the formula with your desired character, such as a hyphen "-", space " ", or comma ",".

How do I extract the text that comes exactly between the first and second underscore?

To extract the text strictly between the first and second underscore, you can use the MID function combined with FIND. The formula would be: =MID(A1, FIND("_", A1) + 1, FIND("_", A1, FIND("_", A1) + 1) - FIND("_", A1) - 1).

Excel & Data Problems