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.

 

[AlKey]

How about this
Enter in B1 and copy down
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),99))

Book1
	A	B
1	PRIVATE LABEL BRAND SALTED ROASTED REGULAR 40OZ 126980474	40OZ
2	HAPPY SALTED ROASTED REGULAR 6.25OZ 1344702821	6.25OZ
3	YOYO FARMS SALTED ROASTED HEDGEHOGS 16OZ 4581328954	16OZ
Sheet1

 

[honard]

Thanks Rick, works like a champ!

 

[grahammichie]

Hi all,

I have gone through each page in this thread and have not been able to find a solution for this:

I want to return the value in-between the parentheses:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Nicky Ring (Grow Learning)

Chloe Fulman (Jeanswest)

Corey Brewer (Uphill)

Charles Post (Skilled People)

David Chow (Easy Dreams)

<colgroup><col style="width: 250px"></colgroup><tbody>
</tbody>

So output would be:
Grow Learning
Jeanswest
Uphill
Skilled People
Easy Dreams

Your help is much appreciated!

 

[AlKey]

Try this
Enter in B1 and copy down

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",99)),99)),")","")

Book1
	A	B
1	Nicky Ring (Grow Learning)	Grow Learning
2	Chloe Fulman (Jeanswest)	Jeanswest
3	Corey Brewer (Uphill)	Uphill
4	Charles Post (Skilled People)	Skilled People
5	David Chow (Easy Dreams)	Easy Dreams
Sheet1

 

[randomXP]

Hello all,

I have another problem in the same register as the one above:

I have a columns of text with cell like below:

[TABLE="width: 559"]
<colgroup><col width="559"></colgroup><tbody>[TR]
[TD="class: xl63, width: 559"]ABCD-01=1,ABCD-02=10,ABCD-04=10,ABCD-06=10,ABCD-08=1,ABCD-09=1[/TD]
[/TR]
</tbody>[/TABLE]

I need to extract as follows: on one row the string ABCD-01, ABCD-02, ABCD-03, etc. and on another row the number 1, 10, 10, etc.

Since it's not the same separator ("=" and ","), I am having problems. Any advice from the magicians around here?

Thank you to all!

 

[randomXP]

Hello all,

I have another problem in the same register as the one above:

I have a columns of text with cell like below:

[TABLE="width: 559"]
<tbody>[TR]
[TD="class: xl63, width: 559"]ABCD-01=1,ABCD-02=10,ABCD-04=10,ABCD-06=10,ABCD-08=1,ABCD-09=1[/TD]
[/TR]
</tbody>[/TABLE]

I need to extract as follows: on one row the string ABCD-01, ABCD-02, ABCD-03, etc. and on another row the number 1, 10, 10, etc.

Since it's not the same separator ("=" and ","), I am having problems. Any advice from the magicians around here?

Thank you to all!

I just realized I wasn't so clear:

If the text above is in A1, I need ABCD-01 in B1, ABCD-02 in C1, etc. and 1 in B2, 10 in C2, etc.

Thank you!

 

[Peter_SSs]

Hello all,

I have another problem in the same register as the one above:

Welcome to the MrExcel board!

Are you after something like this?
Each formula copied across.

Excel Workbook
	A	B	C	D	E	F	G
1	ABCD-01=1,ABCD-02=10,ABCD-04=10,ABCD-06=10,ABCD-08=1,ABCD-09=1	ABCD-01	ABCD-02	ABCD-04	ABCD-06	ABCD-08	ABCD-09
2		1	10	10	10	1	1
Extract Parts

 

[Fluff]

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))

 

[randomXP]

Thank you very much Peter!

For my example above it works, however if the string is larger, it seems to break at some point. I can't seem to figure out why.

For example, for this string:

[TABLE="width: 1394"]
<colgroup><col width="1394"></colgroup><tbody>[TR]
[TD="class: xl65, width: 1394"]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[/TD]
[/TR]
</tbody>[/TABLE]

After ABCD-11 the result is not alright. It returns 10, 10, etc.

Thank you again!
[TABLE="width: 61"]
<colgroup><col width="61"></colgroup><tbody>[TR]
[TD="width: 61"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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))

Thank you a lot!

This ones seem to work for any length. If I am not too bold, is there any online resources which could help me to better understand the formula?

Thank you so much again!