Extract Text From Variable Length String

[Marmit424]

Hi!


I'm trying to extract text from variable strings. Here is a string example:


id="here_is_an_id" name="here_is_a_name" class="here_is_a_class"


I want to extract the text inside name"". However that text length changes, and the fact that it's not the only item in quotes confuses me on how to use the len,mid,search functions. Would someone please help me accomplish this? Thank you so much!

 

[Rick Rothstein]

Unfortunately not, sometimes it will be < span > class="text" name="text" etc and then > textIwant < /span> and sometimes even that span will change.

It's HTML code, isn't it?

< span > is opening syntax
< / span > is closing syntax

Ergo the > to which you refer will be there.

What about the > in front of the "textIwant" that occurs between the < span> and the < /span>?

 

[Marmit424]

One moment

 

[sweetpundir]

=MID(A1,SEARCH("name=",A1)+6,SEARCH("class=",A1)-2-SEARCH("name=",A1)-6)

 

[Marmit424]

I see how that created confusion. The item I want to extract is indeed prefaced by an a href tag, however that tag is not always the beginning of the HTML code. Ex:
< a href="javascript:EntityCrud.showAssetDetailView('APPLICATION',154871)" > Title Here < / a >
and
< a class="actionLinkLite" href="/genres/spirituality" > Spirituality < /a >

So it appears the item is always encased with that a just like you originally thought, my apologies!

 

[AliGW]

So what you want returned is always between the first > and the second < - correct?

 

[Marmit424]

Yes

 

[AliGW]

Try this:

=MID(A1,FIND(" > ",A1)+1,FIND(" < ",A1,2)-FIND(" > ",A1)-1)

Remove the spaces either side of the < and > once you have copied and pasted - they are there only to make the formula appear here.

 

[Rick Rothstein]

I see how that created confusion. The item I want to extract is indeed prefaced by an a href tag, however that tag is not always the beginning of the HTML code. Ex:
< a href="javascript:EntityCrud.showAssetDetailView('APPLICATION',154871)" > Title Here < / a >
and
< a class="actionLinkLite" href="/genres/spirituality" > Spirituality < /a >

So it appears the item is always encased with that a just like you originally thought, my apologies!

Assuming you there will only be one "href" tag per cell, you could use this formula to get the text you want...

=TRIM(REPLACE(LEFT(A1,FIND("<",A1,SEARCH("< a href=",A1)+9)-1),1,FIND(">",A1,SEARCH("< a href=",A1)+9),""))

Edit Note: You must remove the extra spaces after the two highlighted less than symbols.

 

[Marmit424]

Thank you SO MUCH!!! Both of these work wonderfully!

 

[AliGW]

You're welcome!