Function Problems

How to Extract an ID from a Name and ID String in Excel

Ayan Masood Oct 9, 2026 869 views

Question details

The user needs a method to extract specific identification numbers (IDs) that are enclosed in parentheses from cells containing combined names and IDs.

How to Extract an ID from a Name and ID String in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Data cleansing and separation, specifically isolating employee or product IDs trapped inside parentheses alongside other text strings into a dedicated column.
Observed behavior: The IDs are merged with names in a single cell (e.g., 'Lisa Francisco (W2349864)') and must be isolated for database matching, filtering, or reporting purposes.

Before you start

Before applying these formulas, ensure your dataset follows a consistent format where every target ID is reliably enclosed within standard parentheses.

Solution 1Recommended

Use Excel Formulas (TEXTBEFORE/TEXTAFTER or MID/FIND)

Using built-in text manipulation formulas is the most direct and dynamic way to extract the IDs. The result will update automatically if the original text changes.

Microsoft 365 users can take advantage of the newer, simpler TEXTBEFORE and TEXTAFTER functions. Users on older versions of Excel can achieve the exact same result using a combination of the MID and FIND functions.

Select the target cell

Click on the first empty cell in the column where you want the extracted IDs to appear (e.g., cell B2).

Enter the formula for Microsoft 365

If you are using a modern version of Excel, type the formula =TEXTBEFORE(TEXTAFTER(A2,"("),")") and press Enter.

Enter the formula for older Excel versions

If the newer functions aren't available, type the formula =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1) and press Enter.

Fill the formula down

Click and hold the small green square (fill handle) in the bottom-right corner of cell B2, then drag it down to apply the formula to the rest of the rows in your dataset.

Use Excel Formulas (TEXTBEFORE/TEXTAFTER or MID/FIND)

Formula explanation: The MID and FIND combination locates the position of the opening and closing parentheses, calculates the length of the text between them, and extracts exactly that string.

Solution 2

Split Text Using the Text to Columns Wizard

If you prefer not to use formulas, the Text to Columns feature provides a straightforward, wizard-based approach to split the cell content based on delimiters.

Solution 3

Automate the Process with Power Query

For data sets that are frequently updated or imported from external sources, Power Query offers a robust and repeatable solution.

Clean and Process Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet is fully equipped to handle advanced text manipulation. It seamlessly supports the exact same MID and FIND formulas used in Microsoft Excel, providing a lightweight, highly compatible environment for your data processing tasks.

1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing the combined Name and ID data.
2. Input the extraction formula: Click the first empty cell adjacent to your data (e.g., B2), type the formula =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1), and press Enter.
3. Apply to all rows: Hover over the bottom-right corner of the cell until the crosshair appears, then drag it down to extract the IDs for the entire column.
4. Save your work: Click the save icon or press Ctrl+S to save your newly formatted dataset in .xlsx format.

Seamless compatibility with Microsoft Excel (.xlsx) file formats.Full native support for powerful text formulas like MID, FIND, and LEFT.Lightweight software architecture for lightning-fast performance on large datasets.Completely free to use for daily data entry and analysis.

microsoft office alternative - wps office

Frequently Asked Questions

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

A #VALUE! error typically occurs if the text string in the referenced cell does not contain the opening or closing parenthesis. The FIND function fails if it cannot locate the exact character. You can wrap your formula in the IFERROR function, like =IFERROR(MID(...), ""), to leave the cell blank when there are no parentheses.

Can I extract the ID if there are no parentheses, but the ID is separated by a space?

Yes, if the ID is simply the last word in the string separated by a space, you can use the Text to Columns feature with 'Space' as the delimiter, or use a formula combining RIGHT, LEN, and FIND to isolate the last sequence of characters.

How do I remove the original combined data after extracting the IDs?

If you used formulas to extract the IDs, you must first convert the formula results to static values. Select the column with the extracted IDs, copy it (Ctrl+C), right-click the same selection, and choose 'Paste as Values'. Once they are static text, you can safely delete the original combined column.

Excel & Data Problems