I love it when I see people getting excited about Excel commands.
One of my favorite things to teach in an advanced Excel class is the concept of Concatenate, but who can remember that word or how to spell it? Luckily there is an easier way to join together data from one cell and another.
Here is a tutorial on how to create an email list from a list of names.
I copied and pasted a bunch of names off of the internet, some early colonists.
I am using Google Spreadsheet
Now if you are typing this list yourself and can put first and last name into different columns that is best. If you are using EXCEL you can use data to columns in the ribbon to separate the names into separate columns.
In Google Spreadsheet I need to use the function =Split(cell,” “) where I put a space in between quotations to tell the spreadsheet to split the cells up at the space. If you have commas it would look like =split(cell,”, “)
Note the space after the comma since likely you have last name comma SPACE first name.
Drag that formula down the list
There are a couple of functions you may want to know. =LEFT(cell,#) will give you the number of characters from the left side of the string that you designate. For example if you have keeler in cell A1 and in cell B1 you type =LEFT(A1,3) it will return kee… the left 3 characters.
=RIGHT(Cell,#) will do the same thing but from the right side. So if in cell A1 you have keeler and in cell B1 you type =RIGHT(A1,3) you would get ler.
If you want your text from the middle of a text string you can use =mid(cell,starting character number, ending character number).
So if I have the word complementary in cell A1 and then in cell B1 type =mid(A1,7,10) I would get ment returned.
To join two cells together you use the ampersand symbol. (&)
So lets say for my email list the pattern is the first letter of the first name, joined with the last name joined with @mydistrict.k12.ca.us
Then my formula is
=left(cell with first name,1)&cell with last name&”@mydistrict.k12.ca.us”
Remember all text strings need to be in quotations.
Drag that formula down the sheet and you have a list of email addresses you can now copy and paste into your TO field of your email.
1 thought on “Making an Email List from a spreadsheet list”
This would be great too to create google account for students and teachers! Wish I found this post earlier. Doh!