formatting excel sheet to organize data properly

Question

I have an excel sheet which has approximately 2000 Rows. The columns are First Name ,Second Name, email 1, email 2

I want to be able to use all these contacts in one go in gmail in following format

<First Person>email@gmail.com,<Second Person>email@gmail.com,<Third Person>email@gmail.com,..... and so on

I use web based gmail and outlook etc email clients are not allowed so I would be doing a bcc in above format.I downloaded all my contacts but I am not sure as how to convert them in above kind of format i.e. including <> by combining first name,second name and email then , with the contact and organizing them into above format.What application can help me or what exactly should I be doing to ease my work? I am using Libre office and Ubuntu 11.10

Answer 1

The way you've stated it, that this is not a FORMATTING problem: you need to use a FORMULA to combine the data you have into a new data-value, in a new cell.

The nice thing about using a spreadsheet for this is that it's dynamic: if your source data changes, your derived data changes at the same time.

However as others have stated, the gmail anti-spam limits mean that your proposed solution (gmail) won't work if you try to email all 2000 addresses on the same day.

Answer 2

You don't have to create in one go the whole string because some clients have limitations regarding the number of recipients. You can simply create the desired format for each row and then select the ones that you want and paste them in client's field for BCC. You don't have to worry about the fact that you're copying cells instead of the string, because when you're going to paste it the only thing that will be pasted is the text from all of them.

For each row add something like that:

 =CONCATENATE(A2, " ", B2, " <", C2, ">, ")

You could also use the JOIN or CONCATENATE function to specify predefined groups to have the final string in a separate cell.

 =CONCATENATE(D2:D100)

In that example I assume that you're using the shown format, with the trailing comma and space for easier copy directly from the cells.

Answer 3

You could do this with a formula in LibreOffice with some concatenation, something like:

="<" & A1 & " " & A2 & "> " & A3 & "," & "<" & A1 & " " & A2 & "> " & A4

Yet, it wouldn't work because you have a daily 500 recipients limit in Gmail - see Gmail Help for more information.