Help for CONCATENATE function

[mukund lele]

Hi, I just joined this forum today.

I teach 'Personality Development' in a school. All the students names are listed in Col1 of my Excel sheet.

I need to send an email to the parents andor students. I have their email IDs in three columns: Parent1, Student, Parent2. Some cells are empty because all the rows do not have all three cols populated with IDs.

Can the Concatenate function be used to bunch all the available email IDs, separated by a semicolon, so that I can paste it in an email msg in Gmail or Yahoomail?

Thanks for your help.

Have a great day :)

Mukund

PS  Unfortunately I cannot post the file with IDs on this (public) forum, due to privacy reasons. However, if that is essential, I can post it to a specific person who wishes to help me with a solution.

[#2]

Hi,

Yes, you may use

=<Parent1> & ";" & <Student> & ";" <Parent2>

where above are cells with addresses. Not necessary to check if some of them are empty, mail client shall work correctly with extra semicolons (e.g. a@b.c;;x@y.z)

[#3]

Hi Sergei Baklan,

Just tried out your suggestion, thanks

If I pick each cell address at a time and write them into the formula it works fine.

Example

=B6 & ";" & A7 & ";" & A8 & ";" & B9 & ";" & A12

gave a result:

harry2005potter@xxx;rupalipatil1998@xxx;madhuri_jadhav7@xxx;aryansahilm@xxx;leenadumbre@xxx

But I have 25 children in the class which would make the exercise rather tedious. So, I tried giving a range of cells addresses in <Parent1> column

Example:

B6 & ";" & A7:A8 & ";" & B9 & ";" & A12:A15 & ";" & A15:B16

which did not work, and gave me a result:

#VALUE!

Is there a way to work around this?

BTW, I teach Grades VI through IX, and each of those classes has 25-30 kids!!

It will be a big help if you can tweak the formula to enable me to pick up a range of cells.

Ideally,I would prefer the email IDs in the RESULT to run in the sequence of each Row, ie

Row1 <Parent1> <Student> <Parent2> Row2 <Parent1> <Student> <Parent2> Row3 <Parent1> <Student> <Parent2> Row4 <Parent1> <Student> <Parent2> Row5 <Parent1> <Student> <Parent2> and so on, ............................................................ till it reaches  Row25<Parent1> <Student> <Parent2> Row

I have attached a file for better illustration of the requirement.

---

@Sergei Baklan wrote:

Hi,

Yes, you may use

=<Parent1> & ";" & <Student> & ";" <Parent2>

where above are cells with addresses. Not necessary to check if some of them are empty, mail client shall work correctly with extra semicolons (e.g. a@b.c;;x@y.z)

---

[#4]

Hi Mukund,

Unfortunately there was no sample file attached to your post, i'm not sure which range you'd like to concatenate? All available emails for entire class? From your example it's not clear why after B6 you add string from A7 and A8; and after B9 from A12. Sample will help.

And which version of Excel do you have? More exactly Excel 2016 with O365 subscription or any other?

[#5]

Hi Sergei,

When I wrote my last msg to you (with the file attached) and tried to send it, I got a msg saying (something like) "there is an HTML error in your text which has been corrected and if you agree you can post". But the <Post> button was greyed out!. I tried all that I could but the post would not send. Thinking that perhaps the attachment is the problem i removed it, and kept fiddling around and suddenly the msg got posted- but without the attachment. As is evident, I am not au fait with posting msgs on this site!

Sorry about that.

I have attached it now, and this time I hope it will go.

One sheet has only the email IDs which I am looking to extract, and one sheet has the entire class listing.

To answer your other queries:

The reason that I jumped from B6 to string from A7 and A8; and after B9 from A12

is because the other cells in between have no content, so I thought I could skip them.

I am using EXCEL of OFFICE Professional Plus 2010, ver 14.0.7184.5000 (32 bit)

Thank you so much for your patience and support, which I really appreciate.

Mukund

---

@Sergei Baklan wrote:

Hi Mukund,

Unfortunately there was no sample file attached to your post, i'm not sure which range you'd like to concatenate? All available emails for entire class? From your example it's not clear why after B6 you add string from A7 and A8; and after B9 from A12. Sample will help.

And which version of Excel do you have? More exactly Excel 2016 with O365 subscription or any other?

---

uery

[#6]

Hi Mukund,

Thank you for the update.

In latest Excel there is built-in function which solves your issue in one click. In older versions there are no built-in functions which directly concatenate entire range. There are third-party add-ins and/or some macros; or we use workarounds.

Since your data could be changed from time to time it will better to use formulas in helper column which could be next to your table or in separate sheet.

Let keep first cell in this column which is on the top of your list, in attached file J2. When enter into the next cell down the formula

=J2&F3&";"&G3&";"&H3&";"

and copy it down till end of your list. Last cell in that column gives you combined addresses. You may copy this cell and paste into address bar of your email client.

Sample is attached.

Improvement could be to check empty cell and skip extra semicolons, but in your case that's only cosmetic.

[#7]

Hi Sergei,

Many thanks for the brilliant solution.

Hi Sergei,

Many thanks for the brilliant solution.

Which latest version of EXCEL has the built-in function? Is it a FREE download?

A few queries I have about the solution you gave me:

1. In the J3 cell result, how/why did you get a semicolon in the beginning? The rest of the formula is understood.

2.      In J4 cell formula, I am not clear why you used the SUBSTITUTE function. From Excel Help: SUBSTITUTE function

Description. Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. Syntax.

1. Why is SUBSTITUTE not used/required on the formula for cell J5?

Thanks for your help,

Mukund

Thanks a lot for your time

---

@Sergei Baklan wrote:

Hi Mukund,

Thank you for the update.

In latest Excel there is built-in function which solves your issue in one click. In older versions there are no built-in functions which directly concatenate entire range. There are third-party add-ins and/or some macros; or we use workarounds.

Since your data could be changed from time to time it will better to use formulas in helper column which could be next to your table or in separate sheet.

Let keep first cell in this column which is on the top of your list, in attached file J2. When enter into the next cell down the formula

=J2&F3&";"&G3&";"&H3&";"

and copy it down till end of your list. Last cell in that column gives you combined addresses. You may copy this cell and paste into address bar of your email client.

Sample is attached.

Improvement could be to check empty cell and skip extra semicolons, but in your case that's only cosmetic.

---

[#8]

Hi Mukund,

In Excel 2016 for O365 subscribers there is the function TEXTJOIN. In your case the result could be received by

=TEXTJOIN(";",TRUE,F3:H26)

and without any extra semicolons.

1) In J3 semicolon at the beginning appears since the cell F3 is empty. For each empty cell formula adds one semicolon.

2) That's i forgot to remove, SUBSTITUTE definitely is not required here. Please find corrected file attached.