CODEWITHSUNDEEP
excelexcel-formulasubstring
joeyops
joeyops

Reputation: 51

Excel - Extract first letters in a delimited string

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?

Upvotes: 0

Views: 1012

Answers (3)

Magic Bullet Dave
Magic Bullet Dave

Reputation: 9076

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.

Upvotes: 0

Solar Mike
Solar Mike

Reputation: 8415

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

enter image description here

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 :)

Upvotes: 0

JvdV
JvdV

Reputation: 75990

You could use:

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

enter image description here

  • "<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.

Upvotes: 4

Related Questions