Efficient Formula to Extract Text Between Second and Third Hyphen in Excel 2021

[irfman]

Hi,

I have a large dataset stored as string values in Column A. I need to extract the text that appears immediately after the second hyphen (-) and before the third hyphen (-).

Currently, I am using the following formula:
=MID(A2, FIND(CHAR(1), SUBSTITUTE(A2, "-", CHAR(1), 2)) + 1, FIND(CHAR(1), SUBSTITUTE(A2, "-", CHAR(1), 3)) - FIND(CHAR(1), SUBSTITUTE(A2, "-", CHAR(1), 2)) - 1)

However, this formula is lengthy, complicated, and not efficient for a large dataset.

I am using Excel 2021. Are there any simpler and more efficient formulas to achieve the same result?

I appreciate your usual support and assistance.

 

[irfman]

Thank you, everyone, for your prompt assistance. You guys are the best! Love.

 

[Peter_SSs]

Cheers. Glad you got something useful. Thanks for the follow-up.

 

[fahad_ibnfurjan]

=MID(A2:A19,1+FIND("|",SUBSTITUTE(A2:A19,"-","|",2)),FIND("|",SUBSTITUTE(A2:A19,"-","|",3))-FIND("|",SUBSTITUTE(A2:A19,"-","|",2))-1)

 

[Tom.Jones]

Just for fun:

=FILTERXML("<k><m>,&SUBSTITUTE(A2,"-","</m><m>")&"</m></k>","//m[3]")

 

[Peter_SSs]

Just for fun:

=FILTERXML("<k><m>,&SUBSTITUTE(A2,"-","</m><m>")&"</m></k>","//m[3]")

That isn't a valid formula. Presumably you meant this?

=FILTERXML("<k><m>"&SUBSTITUTE(A2,"-","</m><m>")&"</m></k>","//m[3]")

.. but that is by far the slowest, about 14 times as long as the OP's original formula on my machine.

irfman.xlsm
	J	K	L	M	N	O	P	Q
1		Post 1	Post 2	Post 3	Post 3b	Post 4	Post 9	Post 14
2	Time (secs) to recalculate 10,000 rows	0.0624	0.04468	0.04384	0.03487	0.0372	0.02735	0.87403
3	Rank	6	5	4	2	3	1	7
Sheet1

Cell Formulas
Range	Formula
K3:Q3	K3	=RANK(K2,$K2:$Q2,1)