CODEWITHSUNDEEP
excelexcel-formula
onit
onit

Reputation: 2372

How to find the text/number between 2 characters, the last being a non-unique comma using Excel formulas?

The challenge is to find the number between "produto_id:" and the last character, which is sometimes a "]" or a " , ". When it's a "]", it does extract the text, but it doesn't when it's a comma.

The cell value:

linha:2,quantidade:2,preco_cheio:19.9000,preco_promocional:null,preco_venda:19.9000,preco_custo:null,produto_id:27319353,

The formula I'm using, which works when the last character is a ]:

=IFERROR(VALUE(MID(LEFT($AV9;SEARCH("]";$AV9)-1);SEARCH("produto_id:";$AV9)+11;LEN($AV9)));"")

For the occurrences ending in a comma:

=IFERROR(VALUE(MID(LEFT($AV9;SEARCH(",";$AV9)-1);SEARCH("produto_id:";$AV9)+11;LEN($AV9)));"")

I'd appreciate if you could give it a glance and let me know where I'm failing.

Thanks.

Upvotes: 0

Views: 55

Answers (3)

bosco_yip
bosco_yip

Reputation: 3802

In B1, formula copied down :

=MID(A1,SEARCH("produto_id",A1)+11,LEN(A1)-SEARCH("produto_id",A1)-11)

enter image description here

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36840

Give a try on below FILTERXML() formula.

=FILTERXML("<t><s>"&SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,",","</s><s>"),",","</s><s>")&"</s></t>","//s[contains(., 'produto_id:')]"),":","</s><s>") & "</s></t>","//s[last()]")

enter image description here

Upvotes: 1

Mateus Fernandes
Mateus Fernandes

Reputation: 26

Antonio here is a solution for your problem:

Portuguese formula:

=SUBSTITUIR(SUBSTITUIR(EXT.TEXTO(A2;LOCALIZAR("produto_id:";A2;1)+11;NÚM.CARACT(A2)-(LOCALIZAR("produto_id:";A2;1)+10));",";"");"]";"")

English formula:

=REPLACE(REPLACE(MID(A2;SEARCH("produto_id:";A2;1)+11;LEN(A2)-(SEARCH("produto_id:";A2;1)+10));",";"");"]";"")

replace A2 for your cell.

Upvotes: 1

Related Questions