Help with seperating data?

T

trafficbroker

Hello I was wondering if anyone knows a way of seperating chunks of data
into specific lines. i.e:


THIS:

href="3353-12American Baby href="1721-6American Cheerleader
href="10a8-12American City & County href="2928-24American Conservative
href="1072-6American Cowboy href="6035-6American Handgunner
href="3343-6American Heritage href="2498-4American Heritage of
Invention & Technology

BECOMES THIS:

href="3353-12American Baby
href="1721-6American Cheerleader
href="10a8-12American City & County
href="2928-24American Conservative
href="1072-6American Cowboy
href="6035-6American Handgunner
href="3343-6American Heritage
href="2498-4American Heritage of Invention & Technology

Basically is there anyway of parting data into lines at specific
points, in the above case at 'href...'

Thanks
 
M

Max

One play to try ..

Assuming this source data is in A1 down
href="3353-12American Baby href="1721-6American Cheerleader
href="10a8-12American City & County href="2928-24American Conservative
href="1072-6American Cowboy href="6035-6American Handgunner
Click to expand...
etc

Put in B1:
=LEFT(A1,SEARCH("href",A1,2)-1)

Put in C1:
=MID(A1,SEARCH("href",A1,2),LEN(A1)-SEARCH("href",A1,2)+1)

Select B1:C1, copy down until the last row of data in col A

Put in D1:
=OFFSET($B$1,INT((ROWS($A$1:A1)-1)/2),MOD(ROWS($A$1:A1)-1,2))

Copy D1 down until zeros appear,
signalling exhaustion of data extracted from cols B and C

Col D will return the required results.
If necessary, kill the formulas in col D via an in-place:
Copy > Paste speical > check "Values" > OK

--
Rgds
Max
xl 97
 
D

Danny@Kendal

"trafficbroker" <[email protected]>
wrote in message
Hello I was wondering if anyone knows a way of seperating chunks of data
into specific lines. i.e:

THIS:

href="3353-12American Baby href="1721-6American Cheerleader

BECOMES THIS:

href="3353-12American Baby
Click to expand...

Try using Find/Replace to change " href=" to "*href="

Next use Data -> Text To Columns and choose * as the delimiter, uncheck all
the other delimiters.

You should be left with two columns of data which should then be easy enough
to cut-n-paste into one column.
 
Top