Help with seperating data?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

trafficbroker - 14 Dec 2005 12:26 GMT

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

Signature

trafficbroker

Reply to this Message

Max - 14 Dec 2005 14:11 GMT

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

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
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

> Hello I was wondering if anyone knows a way of seperating chunks of data
> into specific lines. i.e:
[quoted text clipped - 25 lines]
> trafficbroker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28710
> View this thread: http://www.excelforum.com/showthread.php?threadid=493345

Reply to this Message

Max - 14 Dec 2005 14:26 GMT

A sample construct is available at:
http://www.savefile.com/files/6326120
Separating_Data_trafficbroker_gen.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Reply to this Message

Danny@Kendal - 14 Dec 2005 14:18 GMT

> Hello I was wondering if anyone knows a way of seperating chunks of data
> into specific lines. i.e:
[quoted text clipped - 6 lines]
>
> href="3353-12American Baby

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.

Reply to this Message