Extract part of text in Excel with condition-CodePudding

I know this questions seems redundant but cannot find an answer. I have a text string such as:

A-B-C-D-E-F

I would like to extract only the first 4 items separated by "-". Result should be

A-B-C-D

Alternatively, I am also looking to extract only the 3rd item:

I have been trying the MID and SEARCH functions with no success. Any help would be well appreciated.

CodePudding user response：

Using TEXTSPLIT (As of this writing only available to Office 365 Insider Beta Channel)

=TEXTJOIN("-",TRUE,INDEX(TEXTSPLIT(A1,"-"),1,{1,2,3,4}))

Change the {1,2,3,4} to 3 for the third.

If one does not have TEXTSPLIT but has TEXTJOIN and is on a PC then:

=TEXTJOIN("-",TRUE,INDEX(FILTERXML("<a><b>"&SUBSTITUTE(A1,"-","</b><b>")&"</b></a>","//b"),1,{1,2,3,4}))

Without TEXTJOIN it will require vba. Like this UDF that mimics TEXTJOIN:

• Formula used in cell B1

=TEXTJOIN("-",,TAKE(TEXTSPLIT(A1,"-"),,4))

• Formula used in cell D1

=TEXTJOIN("-",,DROP(TEXTSPLIT(A1,"-"),,-2))

• Formula used in cell C1

=TEXTJOIN("-",,INDEX(TEXTSPLIT(A1,"-"),,3))

Note: Formulas shown above works for O365 Users, Insiders Beta Channel users only!

However, if you have access to Excel 2019, then you can use either TEXTJOIN() or CONCAT()

• Formula used in cell B9

=SUBSTITUTE(CONCAT("-"&INDEX(FILTERXML("<t><s>"&SUBSTITUTE(A9,"-","</s><s>")&"</s></t>","//s"),ROW(A1:A4))),"-","",1)

• Formula used in cell C9

=SUBSTITUTE(CONCAT("-"&INDEX(FILTERXML("<t><s>"&SUBSTITUTE(A9,"-","</s><s>")&"</s></t>","//s"),3)),"-","",1)

Since OP has mentioned in comments, that OP is using O365 in MAC, hence here is an update.

• Formula used in cell B1

=TEXTJOIN("-",,TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),COLUMN(A1:D1)*99-98,99)))

• Formula used in cell C1

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),COLUMN(C1)*99-98,99))

Or,

• Formula used in cell D1

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),200,100))