Input filtering and organising the data.

Sorry, I really have no idea how to word this so haven't had any luck with google or searching here...

What I need is to be able to paste a whole bunch of data into a single cell, and then for it to be organised into columns and rows etc etc


For example, would it be possible to paste this;

John Harris 12ADHAD 12948124 Timmy Rogers 92OISJS 98752372 Jack Simmons 83IUHGA 9875291 Katie Something 98IAUSHF 38235928735 Smiley Face 82AOFSH 9823523

into the spreadsheet, and have automatically organised it into something nice and playable like this;

http://img238.imageshack.us/img238/5823/goodnr9.jpg



Is it possible?

Originally Posted by supermario2k8

Sorry, I really have no idea how to word this so haven't had any luck with google or searching here...

What I need is to be able to paste a whole bunch of data into a single cell, and then for it to be organised into columns and rows etc etc

....snipped
Is it possible?

Yes it's possible, up to a point. There's a limit on the number of characters you can have in a cell - I think it's slightly variable but circa 1000, but no doubt someone will comment if not.

Given that caveat, and certainly with your example data, I managed it by doing the following.

1. Paste that string into cell A1.
2. Perform a Data-->Text to Columns using the delimited option and tick the space box. When you get to the bit where you can specify the column data format, select all the columns and choose the Text option.
3. Assuming you have now split everything across the columns in Row 1, use the following formulae"

in A3

Please Login or Register  to view this content.

In B3

Please Login or Register  to view this content.

In C3

Please Login or Register  to view this content.

Now copy A3:C3 and copy down.

HTH

Thanks for your help!!


But it turns out the data I'm playing with is already formatted in a way that is workable-with when it's PASTE SPECIAL'd as text or unicode.

So I'm working on the code to auto-paste special a certain cell and... everything should be ok.




Very educational :D