Extract Certain Text from a Cell in Excel

Keep the sample text in Text Extractor!A2, then place the three opening formulas in these cells:

• E2: formula for text before the first pipe
• F2: formula for text after the first pipe
• G2: formula for text between the first 2 pipes

Verify these results in Excel:

Now check an empty middle field by replacing A2 with:

Ticket 104 | | High

G2 should return No match because trimming removes the spaces between the delimiters.

Then replace A2 with:

General note

All three formulas should return No match because the required pipe delimiter is absent.

If your data uses another delimiter, such as "/" or "-", replace "|" in the formulas. Restore the original A2 value before continuing.

If your Excel version doesn’t support TEXTBEFORE or TEXTAFTER, you can use LEFT, RIGHT, MID, and FIND.

With Ticket 104 | Sales | High restored in A2, extract everything before the first pipe:

=IFERROR(TRIM(LEFT(A2,FIND("|",A2)-1)),"No match")

Expected result: Ticket 104

To extract everything after the first pipe, use:

=IFERROR(TRIM(RIGHT(A2,LEN(A2)-FIND("|",A2))),"No match")

Expected result: Sales | High

For the text between the first 2 pipes, use:

=IFERROR(TRIM(MID(A2,FIND("|",A2)+1,FIND("|",A2,FIND("|",A2)+1)-FIND("|",A2)-1)),"No match")

Expected result: Sales

Verify each result in your version of Excel. These formulas return No match when a required pipe is absent. Unlike the opening formulas, however, a segment containing only spaces may return a blank after TRIM.

When departments or priorities can appear in different positions or use inconsistent separators, switch to a known-label formula.

Set up Text Extractor!A1:D7:

Enter descriptive text in column A; columns B and C extract recognized labels, while column D flags incomplete matches.

In Text Extractor!B2, enter this department formula:

=IF(NOT(ISTEXT(A2)),"No match",IF(IFERROR(ISNUMBER(SEARCH("Sales",A2)),FALSE),"Sales",IF(IFERROR(ISNUMBER(SEARCH("Support",A2)),FALSE),"Support",IF(IFERROR(ISNUMBER(SEARCH("Finance",A2)),FALSE),"Finance","No match"))))

The formula checks Sales, Support, and Finance in that order. If several labels appear, this precedence determines the result regardless of their order in the source text.

Next, enter this priority formula in Text Extractor!C2:

=IF(NOT(ISTEXT(A2)),"No match",IF(IFERROR(ISNUMBER(SEARCH("High",A2)),FALSE),"High",IF(IFERROR(ISNUMBER(SEARCH("Medium",A2)),FALSE),"Medium",IF(IFERROR(ISNUMBER(SEARCH("Low",A2)),FALSE),"Low","No match"))))

Here, Excel checks High, Medium, and Low in that order.

Finally, enter the review-status formula in Text Extractor!D2:

=IF(OR(B2="No match",C2="No match"),"Review","Ready")

Select B2:D2 and fill the formulas down through row 7. Your results should be:

Review means that at least one recognized label is missing. Ready means only that both formulas found matching character sequences. It doesn’t indicate approval or independent validation.

Test the following cases individually. Temporarily replace A2, confirm the results in B2:D2, and restore the original value after each test:

• Finance escalation - Low should return Finance, Low, and Ready.
• Numeric 0 should return No match, No match, and Review.
• A blank cell should return No match, No match, and Review.
• sales request - low should return Sales, Low, and Ready.
• Support handoff to Sales - Medium should return Sales, Medium, and Ready because Sales has precedence.

Understand the SEARCH substring limitation

Excel’s SEARCH function is case-insensitive, and it finds character sequences rather than complete words or fields:

• highlight contains High.
• follow-up contains low.

As a result, SEARCH can create unintended matches. For example, Sales follow-up / Medium returns Medium because the formula checks Medium before Low. If a description contained follow-up without High or Medium, the formula could incorrectly return Low.

Use delimiter extraction when a label must occupy a complete field. If capitalization must match, FIND is case-sensitive, although it still matches partial words.

To expand the table, copy B7:D7 downward and enter new descriptions in column A. Review any flagged rows, descriptions containing multiple recognized labels, and possible partial-word matches.

For related cleanup techniques, see Google Sheets Data Cleaning for Finance.

ModelMonkey can inspect spreadsheet context and help adjust formulas in Google Sheets or Excel. For this example, you could ask:

Inspect Text Extractor!A2:D7 and help me add Operations after Finance in the department precedence. Preserve the No match and Review behavior.

Before filling a proposed formula down, check it against the source descriptions, especially where substring matches could affect the result.