Forum Discussion
EmLearnsMicrosoft
Jun 28, 2024Copper Contributor
Formula for extracting multiple occurrences of values following a similar string of text
Hi Tech Community! I am trying to write a formula that will allow me to extract multiple strings of numbers from one cell to another, while filtering out all other irrelevant text from the source cell. For example (see attached photo), I want to be able to extract multiple 8-digit numbers, separated into the output column by "; ", that each occur after the text "ABCD: " within a cell. I have tried multiple complex nesting functions, based off of suggestions on other similar issues, but cannot seem to tailor the functions to fit my exact needs. Any help would be appreciated!!! (I use Excel for Mac, Version 16.84 (24041420))
3 Replies
Sort By
In B2:
=TEXTJOIN("; ", TRUE, IFERROR(TEXTBEFORE(TEXTSPLIT(A2, "ABCD: "), ")"), ""))
- EmLearnsMicrosoftCopper Contributor
HansVogelaar thank you for the speedy response. Unfortunately that didn't exactly work the way I was hoping (see image below).
Your text has ABCD: but the formula uses PMID:
The formula works correctly with ABCD: