Difficulty splitting a column

[upwordz]

I have a column of product descriptions with html tags embedded. I want to extract the beginning of each row up to the first html tag which is always .

I tried running text to columns to split on '<' but because there are several html tags in each row, the rest of the text is not left in tact.

How can I split the data on the first occurrence only?

I have ASAP Utilities but I couldn't figure a solution using those tools either.

3 rows of example data:

RHYNOFIBRE A 5"� x 7/8"� 24X Rhynofibre "A" Silver Resin Fiber </P> </P>Features and benefits</P> <LI>Durable vulcanized fibre backing. <LI>High strength resin eliminates grit shredding. <LI>High initial cutting action. </P>Product specifications</P> <LI>Vulcanized fibre 0,8 mm backing. <LI>Aluminum Oxide abrasive. <LI>Modified synthetic resin bonding. </P>Application suggestions</P> <LI>Machine grinding and sanding of metalwork.

RHYNOFIBRE A 6"� x 7/8"� 24X Rhynofibre "A" Silver Resin Fiber Disc </P> </P>Features and benefits</P> <LI>Durable vulcanized fibre backing. <LI>High strength resin eliminates grit shredding. <LI>High initial cutting action. </P>Product specifications</P> <LI>Vulcanized fibre 0,8 mm backing. <LI>Aluminum Oxide abrasive. <LI>Modified synthetic resin bonding. </P>Application suggestions</P> <LI>Machine grinding and sanding of metalwork.

RHYNOFIBRE A 7"� x 7/8"� 24X Rhynofibre "A" Silver Polycarbonate Resin Fiber </P> </P>Features and benefits</P> <LI>Durable vulcanized fibre backing. <LI>High strength resin eliminates grit shredding. <LI>High initial cutting action. </P>Product specifications</P> <LI>Vulcanized fibre 0,8 mm backing. <LI>Aluminum Oxide abrasive. <LI>Modified synthetic resin bonding. </P>Application suggestions</P> <LI>Machine grinding and sanding of metalwork.

<colgroup><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

your html tag is not being displayed

 

[upwordz]

Yeah. I realized as soon as it posted. Took me a few minutes to get that straightened out.

 

[bkaehny]

=LEFT(A1,FIND("<",A1)-1)

=RIGHT(A1,LEN(A1)-FIND("<",A1)+1)

The first formula takes everything to the left of the first <. The second formula takes everything from the > and on to the right.

 

[upwordz]

Did you try your formula using the data I provided bk? I'm getting data type errors "#VALUE!".

 

[Rick Rothstein]

I have a column of product descriptions with html tags embedded. I want to extract the beginning of each row up to the first html tag which is always '<'.

I tried running text to columns to split on '<' but because there are several html tags in each row, the rest of the text is not left in tact.

How can I split the data on the first occurrence only?

I think copy/pasting the column of data into the next column then, with that copied data still selected, pressing CTRL+H to bring up the Replace dialog box and typing "<*" (without the quote marks) into the "Find what" field, leaving the "Replace with" field empty, and then pressing the "Replace All" button would be a pretty fast way to do it.

 

[greenculture]

I am a webscraper with automation scripts..... if you post the link i can scrape for you.......

also PM for notification

 

[bkaehny]

Did you try your formula using the data I provided bk? I'm getting data type errors "#VALUE!".

Yeah, it works for me, but I'm assuming your data is in cell A1. If it's not, then replace the all the A1 references in what I gave you with the actual cell in your data.

 

[upwordz]

@Rick - Thanks, that worked. I am not sure if the client needs the remainder to be whole (as in your example) or absent the leading text but I have suggested this method.

@BK - Thanks, you were right, I had not paid attention to my cells in the formula. This worked well and I've suggested this method as well.

Between the two solutions you guys posted, we should be all set. Thanks!