Extract A String Between Two Characters

[theaudioguy]

I'm stuck. I need a formula to extract data from between two characters.

For Example, In A1 I have this: COMP_PROG_v1_ABCD_01

I want to extract the value between the 3rd and 4th "_"'s. The number of "_"'s will be consistent but not the # of characters between them. My brain is tired of thinking. Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

I don't doubt there is anything covering this scenario, but this site might help.
https://exceljet.net/formula/extract-multiple-lines-from-a-cell

 

[randomXP]

Beaten 2 it, but another option
=TRIM(MID(SUBSTITUTE($A$1,"=",REPT(" ",100)),SEARCH(",",","&SUBSTITUTE($A$1,"=",REPT(" ",100)),COLUMN(A1)*100-99),100))
=TRIM(MID(SUBSTITUTE($A$1,",",REPT(" ",100)),SEARCH("=",SUBSTITUTE($A$1,",",REPT(" ",100)),COLUMN(A2)*100-99)+1,100))

One more question, please:

Any idea why if the text part has more characters - the formula doesn't work anymore?

For example, for:

ABCDEFG-01=1,ABCDEFG-02=10,ABCDEFG-03=10,ABCDEFG-04=10,ABCDEFG-05=10,ABCDEFG-06=10,ABCDEFG-07=10,ABCDEFG-08=10,ABCDEFG-09=10,ABCDEFG-10=10,ABCDEFG-11=10,ABCDEFG-12=10

<tbody>
</tbody>

It repeats ABCDEFG-09 twice.

Thank you!

 

[Fluff]

You need to increase the number of spaces the formula adds.
=TRIM(MID(SUBSTITUTE($A$3,"=",REPT(" ",500)),SEARCH(",",","&SUBSTITUTE($A$3,"=",REPT(" ",500)),COLUMN(A3)*500-499),500))
=TRIM(MID(SUBSTITUTE($A$3,",",REPT(" ",500)),SEARCH("=",SUBSTITUTE($A$3,",",REPT(" ",500)),COLUMN(A4)*500-499)+1,500))

 

[Peter_SSs]

Instead of continually increasing the spaces and hoping it will be enough, this should work for any number of terms in the string (provided Excel has enough columns. ) I've tested to about 2,000 terms.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	ABCD-01=1,ABCD-02=1,ABCD-03=10,ABCD-04=10,ABCD-05=10,ABCD-06=10,ABCD-07=10,ABCD-08=10,ABCD-09=10,ABCD-10=10,ABCD-11=10,ABCD-12=10,ABCD-13=10,ABCD-14=10,ABCD-15=10,ABCD-16=10,ABCD-17=10	ABCD-01	ABCD-02	ABCD-03	ABCD-04	ABCD-05	ABCD-06	ABCD-07	ABCD-08	ABCD-09	ABCD-10	ABCD-11	ABCD-12	ABCD-13	ABCD-14	ABCD-15	ABCD-16	ABCD-17
2		1	1	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
Extract Parts (2)

 

[randomXP]

Thank you both for all the help!

Both solutions work perfectly for my needs.

Thank you again! You just saved me hours of work.

 

[Peter_SSs]

Thank you both for all the help!

Both solutions work perfectly for my needs.

Thank you again! You just saved me hours of work.

You're very welcome.