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.

 

[tygrrboi]

So there is no way to do this without first adding in all the spaces?

"Adding spaces" all happens behind the scenes. You do not have to do anything. If it stresses you to think about 100 spaces being added in place of an underscore, just imagine it instead as a fluffy cloud or a rainbow. For all intents and purposes the spaces that get added to the cell do not matter because they are washed clean with the power of the Trim() function. Fear not.

 

[KristieNovice]

HI
That last one looks more like what i was trying to achieve, the text to column feature is no use in this instance as I am trying to automate and future proof a worksheet.

Thank you all for your help.

 

[punkzzone]

Hello anyone can help me i need to extract from this :

<dl class="product-spec kvp" style="outline: 1px solid blue;"> <dt class="kvp__key">Kategori</dt> <dd class="kvp__value qa-pd-category" itemprop="category"> Kabel Data </dd> <dt class="kvp__key">Kondisi</dt> <dd class="kvp__value qa-pd-condition"> <span class="product__condition product__condition--new">Baru</span> </dd> <dt class="kvp__key">Berat</dt> <dd class="kvp__value qa-pd-weight"> 200 gram </dd> <dt class="kvp__key"> Merek </dt> <dd class="kvp__value"> Lainnya </dd> </dl>

need result : 200 gram

please help me ...

 

[punkzzone]

sorry my mistake posting ...
this is the right
<dl class="product-spec kvp" style="outline: 1px solid blue;"> <dt class="kvp__key">Kategori</dt> <dd class="kvp__value qa-pd-category" itemprop="category"> Kabel Data </dd> <dt class="kvp__key">Kondisi</dt> <dd class="kvp__value qa-pd-condition"> <span class="product__condition product__condition--new">Baru</span> </dd> <dt class="kvp__key">Berat</dt> <dd class="kvp__value qa-pd-weight"> 200 gram </dd> <dt class="kvp__key"> Merek </dt> <dd class="kvp__value"> Lainnya </dd> </dl>

 

[punkzzone]

<dl class="product-spec kvp" style="outline: 1px solid blue;"> <dt class="kvp__key">Kategori</dt> <dd class="kvp__value qa-pd-category" itemprop="category"> Kabel Data </dd> <dt class="kvp__key">Kondisi</dt> <dd class="kvp__value qa-pd-condition"> Baru </dd> <dt class="kvp__key">Berat</dt> <dd class="kvp__value qa-pd-weight"> 200 gram </dd> <dt class="kvp__key"> Merek </dt> <dd class="kvp__value"> Lainnya </dd> </dl>

 

[Rick Rothstein]

Hello anyone can help me i need to extract from this :

<dl class="product-spec kvp" style="outline: 1px solid blue;"> <dt class="kvp__key">Kategori</dt> <dd class="kvp__value qa-pd-category" itemprop="category"> Kabel Data </dd> <dt class="kvp__key">Kondisi</dt> <dd class="kvp__value qa-pd-condition"> <span class="product__condition product__condition--new">Baru</span> </dd> <dt class="kvp__key">Berat</dt> <dd class="kvp__value qa-pd-weight"> 200 gram </dd> <dt class="kvp__key"> Merek </dt> <dd class="kvp__value"> Lainnya </dd> </dl>

need result : 200 gram

please help me ...

We need more details. Remember, we cannot see your worksheet, so we do not know where anything is located at. For example, is what you posted all in one cell or in separate cells on separate rows? What is it that tells you that you want 200 gram... the fact that it starts with a number?... or that it appears immediately after the word "Berat"?... something else?

 

[punkzzone]

We need more details. Remember, we cannot see your worksheet, so we do not know where anything is located at. For example, is what you posted all in one cell or in separate cells on separate rows? What is it that tells you that you want 200 gram... the fact that it starts with a number?... or that it appears immediately after the word "Berat"?... something else?

A1 : <dl class="product-spec kvp" style="outline: 1px solid blue;"> <dt class="kvp__key">Kategori</dt> <dd class="kvp__value qa-pd-category" itemprop="category"> Kabel Data </dd> <dt class="kvp__key">Kondisi</dt> <dd class="kvp__value qa-pd-condition"> Baru </dd> <dt class="kvp__key">Berat</dt> <dd class="kvp__value qa-pd-weight"> 200 gram </dd> <dt class="kvp__key"> Merek </dt> <dd class="kvp__value"> Lainnya </dd> </dl>

B1 : 200 gram

what is the formula for B1 ?

thanksbefore.

 

[Peter_SSs]

B1 : 200 gram

what is the formula for B1 ?

In order to have the best chance of getting the right formula, you would need to answer the rest of Rick's questions:

What is it that tells you that you want 200 gram... the fact that it starts with a number?... or that it appears immediately after the word "Berat"?... something else?

It might also help clarify if you provided 4 or 5 other possible cell values and their expected results.
See the link in my signature block below for better ways to post sample data - what you are doing is clearly not working very well.

 

[punkzzone]

Sheet1

*	A	B
1	Data	Berat ( gram )
2	<dl class="product-spec kvp" style="outline: 1px solid blue;"> <dt class="kvp__key">Kategori</dt> <dd class="kvp__value qa-pd-category" itemprop="category"> Kabel Data </dd> <dt class="kvp__key">Kondisi</dt> <dd class="kvp__value qa-pd-condition"> Baru </dd> <dt class="kvp__key">Berat</dt> <dd class="kvp__value qa-pd-weight"> 200 gram </dd> <dt class="kvp__key"> Merek </dt> <dd class="kvp__value"> Anker </dd> </dl>	200 ( should be appear this value )

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:151px;"><col style="width:90px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

what is formula for B2 ?

 

[punkzzone]

Excel Workbook
	A	B
1	Data	Berat ( gram )
2	Kategori Kabel DataKondisiBaruBerat 200 gram MerekAnker	200 ( should be appear this value )
Sheet1

need formula for B2 please .. anyone help me ?