![]() |
|
![]() |
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
![]()
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. |
#2
|
||||
|
||||
![]()
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] |
#3
|
|||
|
|||
![]()
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. |
#4
|
||||
|
||||
![]()
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] |
#5
|
|||
|
|||
![]()
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. |
![]() |
Tags |
microsoft excel |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
how to copy addresses in word document to excel/mailmerge list | msnarayanan | Mail Merge | 4 | 10-17-2015 03:17 PM |
2 Email Addresses/ 1 Contact List | Mister Mobius | Outlook | 0 | 06-22-2015 07:56 AM |
Need help making labels from Single Column Word file of addresses | genoMU69 | Mail Merge | 1 | 06-26-2014 03:33 PM |
Why can't I sent emails to addresses not in my contact list? | jdavy | Outlook | 1 | 12-12-2010 09:05 PM |
Creating a Map From a List of Addresses | aleccamp | Excel | 0 | 11-19-2005 03:04 PM |