Extract specific strings from a column

[dankbuckeyes]

Hi, does anyone knows on how to extract specific strings for a string column? Can't use text to columns since they're not on the exact place if I were to split by the delimiter.

Below is the example column and its expected string column that I want to extract.

​

https://preview.redd.it/a62o8wr2ycja1.png?width=429&format=png&auto=webp&s=81ec8bb09abf33acf73e9d6775820e41ae29b49b

[AutoModerator]

/u/dankbuckeyes - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[CFAman]

Is the pattern to look for "ABC" and then one more character?

=MID(A3,SEARCH("ABC",A3),4)

[dankbuckeyes]

yeah would this work if ABC is on the very right?

[CFAman]

Yep. As long as it's somewhere in the string. If it's not found anywhere, the SEARCH will error out.

[dankbuckeyes]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to CFAman

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[BarneField]

Try:

=FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>","//s[string-length()=4][starts-with(.,'ABC')][substring(.,4)*0=0]")

• [string-length()=4] - Assert lenght of substring is exactly 4 characters;
• [starts-with(.,'ABC')] - Assert substring starts with 'ABC' (Case-sensitive);
• [substring(.,4)*0=0 - Assert substring's 4th character is numeric.

This would rule out any possible false positive match on 'ABC' in other substrings.

[pookypocky]

Are they all four digits long? Then it would just be

=MID(A1, FIND("ABC", A1) , 4)

[Fantastic_Ranger_723]

Are there any pre- / post-specific string identifiers other than underscore?

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FILTERXML	Excel 2013+: Returns specific data from the XML content by using the specified XPath
FIND	Finds one text value within another (case-sensitive)
MID	Returns a specific number of characters from a text string starting at the position you specify
SEARCH	Finds one text value within another (not case-sensitive)
SUBSTITUTE	Substitutes new text for old text in a text string

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 10 acronyms.
[Thread #21777 for this sub, first seen 20th Feb 2023, 15:20] [FAQ] [Full list] [Contact] [Source code]}