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?

FILTERXML is not available on Mac or Web Versions of Excel. Hence would favour the IF MID with an array for compatibility. — Magic Bullet Dave, Jan 28 '21 at 15:51

JvdV · Answer 1 · 2021-01-27T12:50:39.443

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.

@SolarMike, no `CONCAT()` is 2019 and up. I'd say an UDF is a good alternative for lower versions. — JvdV, Jan 28 '21 at 07:07

Magic Bullet Dave · Answer 2 · 2021-01-28T15:48:05.327

You can also use (for data in cell B2):

=CONCAT(LEFT(B2,1),IF(MID(B2,ROW(INDIRECT("A1:A"&LEN(B2)-1)),1)="_",MID(B2,ROW(INDIRECT("A2:A"&LEN(B2))),1),""))

MID(B2,ROW(INDIRECT("A1:A"&LEN(B2)-1)),1) - Separates out a text string into an array of single characters.

The IF statement is looking for '_' characters and its result will also be an array of either blank (no character) or the next character after the _.

CONCAT just joins this array together along with the first character extracted using LEFT

Works on PC or MAC versions of Excel.

score 0 · Answer 3 · answered Jan 27 '21 at 15:19

0

Ok, brute force and simple as it has 3 bits:

LEFT(A1,1)&IFERROR(MID(A1,FIND("_",A1,1)+1,1),"")&IFERROR(MID(A1,FIND("_",A1,FIND("_",A1,1)+1)+1,1),"")

I did not put an iferror() around the first part, assuming there would be at least one bit to look at.

The & does the concatenation : much less typing :)

answered Jan 27 '21 at 15:19

Solar Mike

7,156
4
17
32

Does this assume a maximum number of _ present in the data? – Magic Bullet Dave Jan 28 '21 at 15:42
@MagicBulletDave The OP shows a maximum of two underscores and that is what I have written the function for. If you have a situation where you want to deal with more then you need to write your own question, or follow the method and use find() to find the 3rd and 4th underscores. Also why I used iferror() to trap when they don’t exist. – Solar Mike Jan 28 '21 at 15:57
My answer will work with any number of underscores. – Magic Bullet Dave Jan 28 '21 at 15:59
@MagicBulletDave but only if excel 2019 onwards... what happens when people are using an earlier version? – Solar Mike Jan 28 '21 at 16:02
Good point :o) and CONCATENATE I don't think works with arrays. – Magic Bullet Dave Jan 28 '21 at 17:24

Excel - Extract first letters in a delimited string

3 Answers3