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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…