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]

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.

Minor correction to the above formula... I believe it is possible for there to be Line Feed characters and/or Carriage Return characters within the "href" tag structure. I think this should eliminate them...

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

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

 

[Rick Rothstein]

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

Will your cells possibly have other HTML tags in front of the "href" tag? If so, does Ali's formula still work for you?

 

[AliGW]

Rick - see posts #25 and #26.

 

[Marmit424]

Will your cells possibly have other HTML tags in front of the "href" tag? If so, does Ali's formula still work for you?

Just tested it and indeed it does!

 

[Rick Rothstein]

Rick - see posts #25 and #26.

I may have read too much into the question that was asked. If the only thing in the cell is the "href" tag and nothing more, then I would think this formula should work as well...

=REPLACE(LEFT(A1,LEN(A1)-4),1,FIND(">",A1),"")