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.

 

[Pr0metheus]

Hello,

can you help with getting this result? Tried to make formula, but I am not sure if I did it the way it should be done.

Thank you

Sheet1

	A	B
Input	Result
2017-04-20 19:49:10,530 [Thread-835958] DEBUG B_W213St150_v01.js - callback: Value: [[2032]], Timestamp: Št apr 20 19:49:10 CEST 2017, Quality: 192, ErrorCode: 00000000	[Thread-835958]
2017-04-20 19:49:40,763 [Thread-836075] DEBUG B_W213St150_v01.js - callback: Value: [[2027]], Timestamp: Št apr 20 19:49:40 CEST 2017, Quality: 192, ErrorCode: 00000000	[Thread-836075]

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:1082px;"><col style="width:113px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

</tbody>

Formeln der Tabelle

Zelle Formel B2 =TRIM(LEFT(SUBSTITUTE(MID($A2;FIND(" ";SUBSTITUTE($A2;" ";" ";COLUMNS($B:B)))+14;LEN($A2));" ";REPT(" ";LEN($A2)));LEN($A2))) B3 =TRIM(LEFT(SUBSTITUTE(MID($A3;FIND(" ";SUBSTITUTE($A3;" ";" ";COLUMNS($B:B)))+14;LEN($A3));" ";REPT(" ";LEN($A3)));LEN($A3))) <tbody> </tbody>

<tbody>
</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

 

[Martvg]

Would this work?

=MID(A1,FIND("[",A1),FIND("]",A1)-FIND("[",A1)+1)

Best regards,
Mart

 

[Pr0metheus]

Thanks Martvg,

this is definitely better formula. One more question, what if I want to extract just third field, after the second space (let's say expression [Thread is replaced by something else...)?

 

[Martvg]

I'm not sure if I understand your question, do you want to retrieve the value between the second and third space no matter what the value is?
In that case, this should do the trick:

=SUBSTITUTE(MID(SUBSTITUTE(A4," ",REPT(" ",100)),175,100)," ","")

Best regards,

 

[Pr0metheus]

Correct, value between the second and third space. Works perfectly, thanks again.
A.

 

[jbeam4me]

With
A1: COMP_PROG_v1_ABCD_01

This formula returns the characters between the 3rd and 4th underscores (_):

B1: =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,
LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))

In the above example, the formula returns: ABCD

Does that help?

@Ron et. al, I've tried searching the net to no avail:

1. What is the vertical bar (" | ") used for in your formula (i.e., in your formula, there are no other vertical bars but it still works somehow)?
2. What other applications can this symbol be used for?

 

[Ron Coderre]

Apologies for the delayed response. (For some reason, my company keeps blocking this website)
I'll incrementally decompose that formula...See if this helps:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,
LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|","COMP_PROG_v1|ABCD_01")+1,
20),"_",REPT(" ",20)),20))
=TRIM(LEFT(SUBSTITUTE(MID(A1,13+1,
20),"_",REPT(" ",20)),20))"
=TRIM(LEFT(SUBSTITUTE("ABCD_01","_",REPT(" ",20)),20))
=TRIM(LEFT(SUBSTITUTE("ABCD_01","_","                    "),20))
=TRIM(LEFT("ABCD                    01",20))
=TRIM("ABCD                ")
="ABCD"

 

[AlKey]

Try this

[B]=TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",99)),99*3,99))[/B]

Book1
	A	B
1	COMP_PROG_v1_ABCD_01	ABCD
Sheet1

 

[honard]

I need some help with a formula that will provide the xx.xxOZ from text strings such as these:
[TABLE="width: 482"]
<colgroup><col width="482" style="width:362pt"> </colgroup><tbody>[TR]
[TD="class: xl30, width: 482"]PRIVATE LABEL BRAND SALTED ROASTED REGULAR 40OZ 126980474[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 482"]
<colgroup><col width="482" style="width:362pt"> </colgroup><tbody>[TR]
[TD="class: xl30, width: 482"]HAPPY SALTED ROASTED REGULAR 6.25OZ 1344702821[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 482"]
<colgroup><col width="482" style="width:362pt"> </colgroup><tbody>[TR]
[TD="class: xl30, width: 482"]YOYO FARMS SALTED ROASTED HEDGEHOGS 16OZ 4581328954[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for the help

 

[Rick Rothstein]

If your examples are truly representative, it appears that the OZ's text is always the next to the last item in the text. If that is true, then this formula will return it...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),400),200))