How to Extract Multiple Eight-Digit Numbers After Repeated Text in Excel
Question details
The user needs to isolate multiple eight-digit numbers that appear after a specific repeated text marker within a single cell and combine the extracted numbers into a semicolon-separated string.
- Product
- Excel
- Device & OS
- Mac
- Scenario
- Cleaning and extracting specific ID numbers embedded within longer, inconsistent text strings in a single dataset cell.
- Observed behavior
- The user wants to split the text at the 'ABCD: ' marker, capture the eight-digit number following it, discard irrelevant surrounding text, and output the joined results.
Ensure your version of Excel for Mac supports modern dynamic array functions like TEXTSPLIT and TEXTBEFORE, which are included in Microsoft 365 subscriptions.
Use Dynamic Array Formulas (TEXTSPLIT and TEXTJOIN)
This method uses modern Excel functions to split the cell content by the target text marker, isolate the numeric values, and combine them back together with a semicolon.
By nesting TEXTSPLIT, TEXTBEFORE, IFERROR, and TEXTJOIN, you can cleanly parse complex strings in a single step without needing multiple helper columns.
Click on the cell where you want the combined numbers to be displayed (for example, B2).
Type the formula `=TEXTJOIN("; ",TRUE,IFERROR(TEXTBEFORE(TEXTSPLIT(A2,"ABCD: "),")"),""))` into the formula bar. This assumes your original text is in cell A2 and the eight-digit numbers are immediately followed by a closing parenthesis.
If your numbers are not followed by a parenthesis, change the `")"` in the TEXTBEFORE function to match the actual character that follows the number, such as a space `" "` or a comma `","`.
Press Enter to execute the formula. Click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of the column.
Use Power Query for Text Extraction
A robust alternative if you are using an older version of Excel for Mac that does not support the TEXTSPLIT array function.
Try WPS Office for Seamless Spreadsheet Management
If you are encountering limitations with specific functions in your current spreadsheet software or looking for a more cost-effective solution, consider switching to WPS Office. It provides powerful data processing capabilities in a lightweight and familiar environment.
Frequently Asked Questions
Why am I getting a #NAME? error when using this formula?
The #NAME? error typically occurs if your version of Excel does not support the TEXTSPLIT or TEXTBEFORE functions. These functions are exclusive to Microsoft 365 and newer versions. If you are using Excel 2019 or older, you will need to use Power Query or a VBA macro instead.
What if my eight-digit numbers are separated by spaces instead of parentheses?
You can easily modify the TEXTBEFORE function in the formula. Simply replace the closing parenthesis `")"` with a space `" "`. The updated section of the formula would look like `TEXTBEFORE(TEXTSPLIT(A2,"ABCD: ")," ")`.
How can I ensure the formula only extracts exactly eight-digit numbers?
You can add a length check using the LEN function. By combining an IF statement, you can tell Excel to only return the value if `LEN(extracted_text)=8`. Values that do not meet this condition can be replaced with an empty string before the TEXTJOIN function combines them.
Can I use this method to extract numbers without a specific text prefix?
If there is no consistent prefix like 'ABCD: ' to split the text by, this specific formula will not work. Extracting patterns without a clear delimiter usually requires Regular Expressions (Regex) implemented via VBA macros or advanced custom functions in Power Query.
