Excel - Extract first letters in a delimited string

Question

I have data in the form :

ACCOUNT_DOCUMENT
ACCOUNT_FACILILITY_MOD

I would like to extract the first letter (A) and the first letter after every underscore. Final output would be:

AD
AFM

How do I achieve this is Excel?

question from:https://stackoverflow.com/questions/65919405/excel-extract-first-letters-in-a-delimited-string

Answer 1

You could use:

=CONCAT(LEFT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>","//s")))

• "<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>" - Using SUBSTITUTE() to create a valid XML-construct to be processed with FILTERXML().
• "//s" - The appropriate XPATH expression to return all nodes as an array.
• LEFT() - Used to retrieve all characters at the 1st index of each element of the array. Note that if the 2nd parameter is left out this will default to a length of just 1.
• CONCAT() - Used to piece all these single characters back together.

Note that my example is done through dynamic array functionality in Microsoft365, but if this is done in Excel 2019 one would need to confirm through CtrlShiftEnter

Backbone of this formula is the way we using FILTERXML() to split the string up in pieces. If you are interested, you can find more information about it over here.