Identify & Remove Specific Data Set From String

[agabor]

[TABLE="width: 1790"]
<tbody>[TR]
[TD="width: 1790"]Hello,
I need help identifying a formula that will locate the "country of origin" attribute in a data set (Example below) and extract it. All of the html exists in a single cell & I want all of it to remain there, except for the country of origin portion. However, each row might have a different value or character length for the country of origin (USA, Philippines, China etc.). So, I need to write a formula that identifies the country of origin string & removes all the values in between the <p></p> tags. I highlighted the portion below in red that I would like to strip out.

I hope this makes sense....


<p><strong>Coating</strong> : No Coating</p><p><strong>Construction</strong> : Cut N Sew</p><p><strong>Country of Origin</strong> : Philippines - Subject to change</p><p><strong>Cuff Type</strong> : No Cuff</p><p><strong>Glove Type</strong> : Ambidextrous</p><p><strong>Length</strong> : Standard</p><p><strong>Material</strong> : 100% Cotton</p><p><strong>Material Weight</strong> : Lightweight</p><p><strong>Reversible</strong> : Yes</p><p><strong>Shell Color</strong> : White</p><p><strong>Size</strong> : Ladies (Fits Medium)</p>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[jproffer]

I think this will do it:

=MID(A1,FIND("Country of Origin : ",A1)+20,LEN(A1)-(FIND("Country of Origin : ",A1)+20)-(LEN(A1)-FIND("Cuff Type : ",A1)))

 

[agabor]

Thank you for your response, jproffer. I should have added that the attribute after country of origin is not always going to be "cuff type". It could be a number of different attributes. Will that affect the results of this formula?

 

[jproffer]

Absolutely. We could find the space after the country name, but then if you have a multi-word country name, it would only return the first word.

We could find the next " : " in the string and subtract back from there, but if it's not always going to be after "Cuff Type", then that wouldn't be ideal either.

Is it in the cell like that? With the line breaks, I mean...as in [Alt]+[Enter] to the next line? We MAY (I've never tried, but I will) be able to pick up the line break character and stop at that.

Bottom line of it is, we have to find something consistent to stop the MID function at the end of the country name. Let me know about the line breaks and I'll see what I can come up with.

 

[agabor]

Could we trigger off the ending paragraph tag?

< p >< strong >Country of Origin< / strong > : Philippines - Subject to change < / p >

 

[jproffer]

I'd forgot about the HTML tags you said you had. I'm not all that familiar with HTML, but what you just posted looks nothing like what you originally posted. (missing " : ", different spacing, etc)...but anyhow, if those tags are actually in the string, then yes. Maybe this:

=MID(A1,FIND("Country of Origin",A1)+23,LEN(A1)-(FIND("Country of Origin",A1)+20)-(LEN(A1)-FIND("<p></p>",A1,FIND("Country of Origin",A1)+24)))
I couldn't get it to work with testing, but I suspect that's something I'm doing wrong because of the tags. See what happens.

 

[agabor]

I apologize. I posted the original post with the HTML tags, but the message box formatted it. So, in my last post, I put spaces between each character, so the HTML wouldn't get formatted out. I wanted you to be able to see every character being used in the cell.