Forum Discussion

Rfire777's avatar
Rfire777
Copper Contributor
Sep 30, 2022

How to split the given text

Hi Everyone! How can i split the text with two hyphen but need the result in only two parts, 1st is the two letter code and in second column need the rest of the text as shown in attached photo
excel
Excel for web
Harun24HR's avatar
Harun24HR
Silver Contributor
Oct 01, 2022

Rfire777 Excel for web and desktop O365-

=TEXTSPLIT(SUBSTITUTE(A1,"_","|",1),"|")

 For Excel 2013 and later-

=FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>",1)&"</s></t>","//s[1]")
=FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>",1)&"</s></t>","//s[2]")

Excel older than 2013-

=LEFT(A1,SEARCH("_",A1)-1)
=MID(A1,SEARCH("_",A1)+1,100)