List of names in alphabetical order
Solved
alma
-
shana -
shana -
Hello,
I'm not very skilled as you will see: I need to create a list of clients, with addresses.
I would like to present it in alphabetical order, and also have the possibility to use it for creating envelopes.
ex:
Mr. Jean Dupont
14, rue des Oliviers
13000 Marseille
So far, in Excel, I can enter the names and addresses in a table... But then, one thing bothers me, the title "Mr. or Mrs." which will prevent sorting alphabetically.
How can I do this?
And then, how can I print my envelopes with the names?
Is Word better than Excel?
Thank you for your help.
Configuration: Windows 7 / Firefox 9.0.1
I'm not very skilled as you will see: I need to create a list of clients, with addresses.
I would like to present it in alphabetical order, and also have the possibility to use it for creating envelopes.
ex:
Mr. Jean Dupont
14, rue des Oliviers
13000 Marseille
So far, in Excel, I can enter the names and addresses in a table... But then, one thing bothers me, the title "Mr. or Mrs." which will prevent sorting alphabetically.
How can I do this?
And then, how can I print my envelopes with the names?
Is Word better than Excel?
Thank you for your help.
Configuration: Windows 7 / Firefox 9.0.1
16 answers
Hello,
When creating a database that will also be used for mail merging, it must be done properly, meaning one column per piece of information, and therefore:
- a column for the title (Mr., Mrs., Ms.)
- a column for the first name
- a column for the last name
- a column for address line 1
- a column for address line 2
- a column for address line 3
- a column for the postal code
- a column for the city
- a column for the country (if working internationally)
- etc.
This will allow you to perform all the processes you deem necessary: sorting by last name, by postal code, by city...
Mail merging with feminine and masculine according to the title, etc.
m@rina
- "On the office forum, we ask questions about office software..."
- "Really???"
When creating a database that will also be used for mail merging, it must be done properly, meaning one column per piece of information, and therefore:
- a column for the title (Mr., Mrs., Ms.)
- a column for the first name
- a column for the last name
- a column for address line 1
- a column for address line 2
- a column for address line 3
- a column for the postal code
- a column for the city
- a column for the country (if working internationally)
- etc.
This will allow you to perform all the processes you deem necessary: sorting by last name, by postal code, by city...
Mail merging with feminine and masculine according to the title, etc.
m@rina
- "On the office forum, we ask questions about office software..."
- "Really???"
Thank you Marina!
I thought I had to go in that direction...
So I will have to re-enter everything... 200 addresses and names... unless there is a solution??
Then, I will try sorting by title, name, ... and maybe I will have to ask for your help again! We'll see...
Thanks again and see you very soon.
I thought I had to go in that direction...
So I will have to re-enter everything... 200 addresses and names... unless there is a solution??
Then, I will try sorting by title, name, ... and maybe I will have to ask for your help again! We'll see...
Thanks again and see you very soon.
Thank you, Marina! I simply clicked on a cell and then sorted, and it all worked. I have the sort in alphabetical order!! Great.
Now, I have a list of clients entered in Word and separated by a space:
Mr. Jean Dupont
34 rue des Bleuts
78000 Versailles
SPACE
Ms. Anne X
........ etc.
Is there a way to copy and paste them into an Excel table and avoid having to rewrite everything?
My version of Excel is the one purchased in 2010: sorry I don't know more... Really not skilled...
Now, I have a list of clients entered in Word and separated by a space:
Mr. Jean Dupont
34 rue des Bleuts
78000 Versailles
SPACE
Ms. Anne X
........ etc.
Is there a way to copy and paste them into an Excel table and avoid having to rewrite everything?
My version of Excel is the one purchased in 2010: sorry I don't know more... Really not skilled...
Hello,
In computing, we don't do the same thing multiple times, that's the principle.
Your list can be retrieved in Excel.
You need to start by working in Word.
What I'm going to explain may seem long, but it can be done in 2 and a half minutes!! ;)
- Do a find and replace and
* replace all double paragraph breaks with some character (for example #)
* then replace all paragraph breaks with a tab,
* then replace all the # with a paragraph break
Note: In the Find and Replace dialog, a paragraph break is ^p and a tab is ^t
Then you replace the spaces between Mr., Mrs. and Miss with a tab:
Search for: Mr. (don't forget the space)
Replace with: Mr.^t
etc.
When everything is done, copy and paste it all into Excel.
m@rina
--
- "On the office forum, we ask questions about office automation..."
- "Really???"
In computing, we don't do the same thing multiple times, that's the principle.
Your list can be retrieved in Excel.
You need to start by working in Word.
What I'm going to explain may seem long, but it can be done in 2 and a half minutes!! ;)
- Do a find and replace and
* replace all double paragraph breaks with some character (for example #)
* then replace all paragraph breaks with a tab,
* then replace all the # with a paragraph break
Note: In the Find and Replace dialog, a paragraph break is ^p and a tab is ^t
Then you replace the spaces between Mr., Mrs. and Miss with a tab:
Search for: Mr. (don't forget the space)
Replace with: Mr.^t
etc.
When everything is done, copy and paste it all into Excel.
m@rina
--
- "On the office forum, we ask questions about office automation..."
- "Really???"
I've just entered 3 rows of clients for testing: title, first name, last name (column 3), address, city.
My goal is to sort the last names in alphabetical order.
I thought I could do this by selecting column 3. But ... it didn't work! The first names and addresses aren't following correctly!!
Thank you again for your help.
My goal is to sort the last names in alphabetical order.
I thought I could do this by selecting column 3. But ... it didn't work! The first names and addresses aren't following correctly!!
Thank you again for your help.
It doesn't work because I think we don't understand each other.
I have:
column 1: Mr. or Mrs. or Miss
column 2: first name
column 3: last name
column 4: street
column 5: city
I would like an alphabetical order based on column 3 (last names). And with your method, the sorting is done by column 1 (titles: Mr. and Miss and Mrs.).
Thank you!
I have:
column 1: Mr. or Mrs. or Miss
column 2: first name
column 3: last name
column 4: street
column 5: city
I would like an alphabetical order based on column 3 (last names). And with your method, the sorting is done by column 1 (titles: Mr. and Miss and Mrs.).
Thank you!
So your client list is in Word and not in Excel?? You were talking about Excel earlier... I admit I no longer understand...
You need to be precise... What exactly is this list like?
It would be best if you could give us an example (with fake names and fake addresses) and put it here, not forgetting to give us the link:
https://www.cjoint.com/
m@rina
--
- "On the office forum, we ask questions about office work..."
- "Oh really???"
You need to be precise... What exactly is this list like?
It would be best if you could give us an example (with fake names and fake addresses) and put it here, not forgetting to give us the link:
https://www.cjoint.com/
m@rina
--
- "On the office forum, we ask questions about office work..."
- "Oh really???"
Yes, yes: my list is in Excel, no problem! And thanks to you, I solved my sorting issue.
But I have another list of 200 names in Word that I want to transfer to Excel.
It looks like this, very simple:
entered in Word and separated by a space:
Mr. Jean Dupont
34 rue des Bleuts
78000 Versailles
SPACE
Mrs. Anne X
........ etc.
It seems difficult to avoid re-entering it in Excel...
But I have another list of 200 names in Word that I want to transfer to Excel.
It looks like this, very simple:
entered in Word and separated by a space:
Mr. Jean Dupont
34 rue des Bleuts
78000 Versailles
SPACE
Mrs. Anne X
........ etc.
It seems difficult to avoid re-entering it in Excel...
Thank you, Scorpion Violet, for your answer.
I will re-enter my list because I don't see any solution.
I don't know what the cvs format is, and if you don't think it's going to work, I'll drop it.
Thank you again and have a good day.
I will re-enter my list because I don't see any solution.
I don't know what the cvs format is, and if you don't think it's going to work, I'll drop it.
Thank you again and have a good day.
Marina, here is the link http://cjoint.com/?3AtpFfGO7fd
This is an example where I modified the names.
I have several pages like this that I entered some time ago, not thinking that later I would need to use them (in alphabetical order, by city, ...).
I would like to transfer them into an Excel spreadsheet.
I should add that I have Excel 2007.
Thank you.
This is an example where I modified the names.
I have several pages like this that I entered some time ago, not thinking that later I would need to use them (in alphabetical order, by city, ...).
I would like to transfer them into an Excel spreadsheet.
I should add that I have Excel 2007.
Thank you.
alma13
I created a macro for your Word addresses.
Here is the file you gave me, to which I have added the macro.
You just need to run the macro called Addresses.
This macro converts your addresses into Excel-compatible formats. Once the macro is run, you can copy and paste into Excel.
However, since there are a variety of formats in your addresses, it's difficult to account for everything.
The macro works for 3-line addresses and for the titles Mr., Mrs., and Miss.
For addresses longer than 3 lines, you’ll need to tweak a bit.
Test it and let us know.
m@rina
--
- "On the office forum, we ask questions about office tools..."
- "Really???"
I created a macro for your Word addresses.
Here is the file you gave me, to which I have added the macro.
You just need to run the macro called Addresses.
This macro converts your addresses into Excel-compatible formats. Once the macro is run, you can copy and paste into Excel.
However, since there are a variety of formats in your addresses, it's difficult to account for everything.
The macro works for 3-line addresses and for the titles Mr., Mrs., and Miss.
For addresses longer than 3 lines, you’ll need to tweak a bit.
Test it and let us know.
m@rina
--
- "On the office forum, we ask questions about office tools..."
- "Really???"
Hello,
When I opened the link, my computer refused to activate the macro.
I asked it to activate, and I ended up with a normal Word document.
If I copy and paste the Word document into Excel, the following message appears:
"the size and shape of the clipboard data do not match the selected area"
And if I still paste the data, it ends up "in a jumble" in the Excel table.
I should also add that the Word document is marked "read-only": is that normal?
When I opened the link, my computer refused to activate the macro.
I asked it to activate, and I ended up with a normal Word document.
If I copy and paste the Word document into Excel, the following message appears:
"the size and shape of the clipboard data do not match the selected area"
And if I still paste the data, it ends up "in a jumble" in the Excel table.
I should also add that the Word document is marked "read-only": is that normal?
It works, but now, I don't know how to create a macro on the actual Word document...
Marina, if you give me the method, don't be surprised because I don't think I'll be able to respond before Monday.
Thank you and have a good weekend!
Marina, if you give me the method, don't be surprised because I don't think I'll be able to respond before Monday.
Thank you and have a good weekend!
Hello,
You press Alt+F11 to retrieve the macro, then look here:
https://faqword.com/index.php/word/tutos/toutes-versions/615-le-b-a-ba-du-vba#forum
m@rina
--
- "On the office forum, we ask questions about office software..."
- "Really???"
You press Alt+F11 to retrieve the macro, then look here:
https://faqword.com/index.php/word/tutos/toutes-versions/615-le-b-a-ba-du-vba#forum
m@rina
--
- "On the office forum, we ask questions about office software..."
- "Really???"
Thank you for your help, Marina.
I will study all of this.
If there's a problem, I'll get back to you.
Have a good day.
I will study all of this.
If there's a problem, I'll get back to you.
Have a good day.
Hello,
When sorting a column in Excel, especially DO NOT SELECT... In the latest versions, Excel alerts... but well, it's really something unnecessary and dangerous.
So, select one of the cells in the column, and just click on the sort button (A-Z).
Otherwise, to avoid re-entering everything, you can use the Convert function which will allow you to separate, for example, your Mr., Mrs., from the name.
What version of Excel are you using?
m@rina
--
- "On the office forum, we ask questions about office software..."
- "Oh really???"
When sorting a column in Excel, especially DO NOT SELECT... In the latest versions, Excel alerts... but well, it's really something unnecessary and dangerous.
So, select one of the cells in the column, and just click on the sort button (A-Z).
Otherwise, to avoid re-entering everything, you can use the Convert function which will allow you to separate, for example, your Mr., Mrs., from the name.
What version of Excel are you using?
m@rina
--
- "On the office forum, we ask questions about office software..."
- "Oh really???"
thannnkkk youuu