Contents
Quiz Question
I have a list of email addresses in Excel. I want to send a mail to all of them. Any email software requires email addresses to be separated by a semicolon. Obviously, the list in Excel is not delimited with anything – it is like a table.
So tell me the fastest and smartest way to convert it from tabular Excel data to a semicolon delimited list.
Example: Input
If the data was like this…
Example: Expected Output
someone@maxoffice.biz; boss@maxoffice.biz; finance@maxoffice.biz; chairman@maxoffice.biz; everyperson@maxoffice.biz; katrin@maxoffice.biz
How to answer
- Post your answer as a comment on this article in WordPress
- Answers posted in Facebook pages, LinkedIn, or any other site will not be eligible
- The quiz is open till 11:59 PM IST on Monday, 3rd March 2014
- The person who answers correctly gets the prize
- If multiple persons (say n)have answered correctly, we will choose the winner randomly
- Random number between (1, n) will be generated using RANDBETWEEN function
- The n th correct post in chronological order will be considered the winner
- If the winner is from outside India, USD 20 Amazon Gift Voucher is the prize
- If the winner is from India, she can choose from Amazon or Flipkart (INR 1500)
- The winner will be announced in a post on Tuesday, 4th March 2014
- The gift voucher will be mailed to the winner using the email id used in the reply
-
Enjoy
4 Responses
Go to “Formulas” tab, select “Text” tab then select “CONCATENATE”.
1) In B7 put the formula, just duplicates the A7 value:
=A7
2) In B6, put the formula below:
=A2&”;”&B7
Great Quiz.
There are two three ways to do it.
1. If you are using outlook 13 or thunderbird or gmail interface on web (For which I have tested it) then just simply copy the address in excel and paste it directly on to “To” text box. Addresses would always be separated by semicolon. So this is a default feature in many mail clients and on at least three where I have tested it.
2. If some mail client do not accept this then in a separate cell use formula =concatenate(a2,”;”) and copy the same formula for all cells. Now copy all email addresses and paste on to “To” text box.
3. if all email addresses are ending in same domain like in your case all ending in .biz then just replace .biz with .biz; for that do ctrl + H and replace all.
If you want output in one line as you mentioned in your post then after doing step 2 select all and paste special with transpose option. All addresses would be at one line.
1. Open New Mail (Outlook 2010)
2. Click on ‘To…’ field, paste the email addressed copied from Excel in this field. It will appear as a list (tabular, similar to Source), click Ok.
3. The mail will now show the email addresses with a semicolon delimiter in the ‘To…’ field.
4. This works for ‘Cc…’ and ‘Bcc…’ fields too.