How to extract text before the second instance of a character?

Question

Example:

FIRST_LAST_TITLE_BRANCH

I want just FIRST_LAST, only the data before the second "_". Some rows have more data than others (like "FIRST_LAST_TITLE_BRANCH_YEAR"), so flash fill keeps messing up on the rows with additional information.

Answer 1

> ... How to extract text before the second ( "_" )

The function reads just like the question....

=TEXTBEFORE(A1,"_",2)

Answer 2

Hello Oliver,

Welcome to the Microsoft Community.

You can achieve this using a formula in Excel. Here’s a step-by-step guide:

1. Identify the position of the second underscore:
   • Use the FIND function to locate the first underscore.
   • Use the FIND function again to locate the second underscore, starting the search after the first underscore. FIND, FINDB functions - Microsoft Support
2. Extract the text before the second underscore:
   • Use the LEFT function to extract the text up to the position of the second underscore. LEFT, LEFTB functions - Microsoft Support

Here’s the formula you can use:

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

• FIND("_", A1) finds the position of the first underscore.
• FIND("_", A1, FIND("_", A1) + 1) finds the position of the second underscore.
• LEFT(A1, FIND("_", A1, FIND("_", A1) + 1) - 1) extracts the text up to the position before the second underscore.

Notice: A1 is my assumed location of your source text, which you can replace with your actual cells.

This formula should work regardless of how many underscores are in your text.

I guess perhaps you have some cells that only contain one underscore, in this case the prior formula will return a #VALUE! error. To handle cases where there is only one underscore, you can use the IFERROR function to provide an alternative result when the formula encounters an error.

Here’s an updated formula that returns the entire text if there’s only one underscore: =IFERROR(LEFT(A1, FIND("", A1, FIND("", A1) + 1) - 1), A1) This formula works as follows:

• LEFT(A1, FIND("_", A1, FIND("_", A1) + 1) - 1) extracts the text before the second underscore.
• IFERROR(..., A1) returns the entire text in A1 if the extraction formula results in an error (e.g., when there’s only one underscore).

I think this will be a good solution to your needs. Have a try and feel free to post back!

Best Regards,

William.Y | Microsoft Community Support Specialist