Function Problems

How to Use a Begins-With Condition in an Excel Formula

Maira Mehtab Sep 28, 2026 871 views

Question details

The user needs to test the starting characters of an item number in a cell and extract a specific number of characters based on that detected prefix.

Product: Excel
Device & OS: not provided
Scenario: Extracting dynamic text string lengths depending on specific starting characters, such as 'BE' or 'Y', without relying on overly complex nested formulas.
Observed behavior: The goal is to accurately evaluate cell prefixes and return a customized string length using efficient formulas or lookup tables.

Before you start

Identify the exact source cells containing your item numbers and clearly define the prefixes along with the character counts you want to extract for each before building your formula.

Solution 1

Use Nested IF and LEFT Functions for Simple Conditions

Best for scenarios where you only have two or three fixed prefixes to check.

Select the target cell

Click on the blank cell where you want the extracted text result to appear.

Enter the IF and LEFT formula

Type =IF(LEFT(C2,2)="BE",LEFT(C2,7),LEFT(C2,6)) into the formula bar. This checks if the first two characters of cell C2 are 'BE'. If true, it returns the first 7 characters; otherwise, it returns the first 6 characters.

Apply to the remaining cells

Press Enter, then drag the fill handle down to apply the formula to the rest of the rows in your column.

Solution 2Recommended

Create a Lookup Table with XLOOKUP for Multiple Prefixes

Highly recommended when dealing with many different prefixes, as a lookup table is much easier to maintain than multiple nested IF statements.

Advanced Spreadsheet Features

Use Advanced Formulas Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and lookup functions, including IF, LEFT, and XLOOKUP. This allows you to seamlessly process complex text extraction and conditional formatting just like you would in Microsoft Excel.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the item numbers.
2. Set up your lookup table: Create a clean lookup table specifying prefixes and character lengths in an empty section of your sheet.
3. Apply the logical formula: Type your IF or XLOOKUP formula directly into the formula bar. WPS Spreadsheet auto-suggests function arguments as you type.
4. Extract your data: Press Enter and drag the fill handle to apply the begins-with text extraction to your entire dataset.

Full compatibility with Microsoft Excel formulas and functionsEasily construct lookup tables for begins-with condition extractionLightweight, fast, and completely free to useUser-friendly interface for building and debugging complex formulas

Frequently Asked Questions

Can I use wildcard characters like an asterisk (*) for a begins-with condition?

While functions like COUNTIF or VLOOKUP support wildcards (e.g., 'BE*'), logical functions like IF combined with LEFT are generally better for strict prefix extraction when you need to return specific character counts based on the prefix.

What if my item numbers have varying prefix lengths?

If your prefixes vary in length (e.g., some are 1 character, others are 2 or 3), you can use nested IF statements to evaluate the different lengths, or standardize a lookup table and use the IFERROR function to cascade through different LEFT character counts.

Why is XLOOKUP returning an error in my extraction formula?

An error usually occurs if the extracted prefix is not found in your lookup table. You can utilize the 'if_not_found' argument built into XLOOKUP to return a default character length and prevent #N/A errors.

Excel & Data Problems