-1

I am using Excel 2010. In my workbook, there is a vertical list of emails addresses in column A1 to A50. I tried joining these emails together using concatenation like concatenate(A1,";",A2,";",...). But it is getting super lengthy and even very difficult to write. Do we have a formula to do this rather than manually selecting each cell and adding ; symbol so that I can copy paste the data into email To field? The email list keeps increasing or decreasing as people in distribution list keep moving in or out of organisation.

Any macro suggestion is also fine.

Thanks in advance

miken32
  • 42,008
  • 16
  • 111
  • 154
Anup
  • 45
  • 8
  • 1
    Use the newer TEXTJOIN function. If you don't have TEXTJOIN then https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050?s=1|86.8495#50719050. –  Aug 29 '18 at 18:11
  • Thanks mate! Let me check. – Anup Aug 29 '18 at 18:18

2 Answers2

0

I use the ampersand & like this:

enter image description here

Works fine,just drag down... cellC2 shows the formula used in cell B2

Replace the comma with a semi colon or whatever separator you need.

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • I already tried to mark it as answer..But it shows this statement "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score." :( – Anup Aug 31 '18 at 07:43
  • You should see a Tick on the top left to use to accept it as an answer, below the arrows for a vote. – Solar Mike Aug 31 '18 at 07:46
  • Done. Sorry new here. – Anup Aug 31 '18 at 07:47
0

Assuming your email ids are in A1 to A20.

Paste the below formula in B1

=A1

and paste the below formula in B2

=B1&"; "&A2

and Select cell B2 and drag down up to B20 or Select the range B2 to B20 and press CTRL+ D

enter image description here

Karthick Gunasekaran
  • 2,697
  • 1
  • 15
  • 25