I want to add the following type of list in microsoft excel 2013:



1
Mary
5th Lane
2
Joe
Win Road

I want the number & address to be in one cell of its own 7 I can achieve this individually. However, I have a list of 1000 to put in, how do I achieve this by way of pasting all of it in but for each address to be in a cell of its own & for it to stay in the above format.

So for example the following must be in a cell of it's own the way you see it:

3
Richard Coe
Farm Street

The reason I want to do this is because I want to randomize the long list and for instance number 1 would appear anywhere from 1 to 1000 and could appear in the center.

I would very much appreciate anyone that could help me.

Put the name & address parts in different columns on the same row. That is how such data are normally stored; it will also facilitate your randomisation, since you can randomize by row.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you very much for your input macropod, but this would be a very daunting task because the way I have a list is in the order I explained as seen in the following example.

Example:

3
Richard Coe
Farm Street

As opposed to how you have mentioned i.e. I assume the following:

3 Richard Coe Farm Street

Which you will note is the exact same name and address, however I would prefer my format because I have a 1000 of them and you will appreciate the daunting task in me having to put them in the order you have stated.

Unless of course there is a way in which to swiftly achieve the order you stated and however to put them back in the exact same format as I had them.

The way I suggested can be achieved through an Offset formula in A1 on a new worksheet:
=OFFSET(Sheet1!$A$1,(ROW()-1)*2+COLUMN()-1,0)
copied to B1, then down as far as needed (~500 rows). Your preferred method entails editing every entry...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I think that with Excel 2013 you will have to use some VBA code as shown at this link (at the bottom):

https://answers.microsoft.com/en-us/...158d67d?auth=1

and then use a formula like:

=TEXTJOIN(" ",TRUE,OFFSET($A$1,(ROW(1:1)-1)*3,,3))

(with your data as from A1 and downwards).

In office 365 Excel there is a built-in function TEXTJOIN so here you don't need the VBA code.