Quick Excel Question

[Mollox]

Guys, sorry this should be easy and its annoying as I used to know how to do this....

I basically have a list of email addy's in separate cells in a column, such as:

[email protected]

[email protected]

[email protected]

and I want to convert them to a semi colon-separated list for pasting straight into an outlook address field, such as:

[email protected]; [email protected]; [email protected]

Now I know the basic function is CONCATENATE but how to I go about separating them with a semi-colon and a space? Is that a separate function I run on the data set first?

Can normally google this stuff instantly but I've lost the touch today.

Cheers

M

[Mac]

Depends how quick and clean you want it - you can't just include ; as it's 'special' and you'll get a naming error I think.

A1: [email protected]

A2: [email protected]

A3: [email protected]

A4: ;

A5=Concatenate(A1,A4," ",A2,A4," ",A3)

....will do the trick, but dirty.

[Mac]

...also, if you take the space out it should work too:

A1: [email protected]

A2: [email protected]

A3: [email protected]

A4: =concatenate(a1,";",a2,";",a3)

Don't have Excel in front of me otherwise I'd try it - on my home Mac.

[Mollox]

Thanks Mac, I think concatenate is limited to 30 values though?

Anyhow, my brain started working and I cracked it. Spose I should show how I did it:

1. Copied column range of email addresses

2. In a new cell, Paste Special > Transpose to give the values in horizontal cells

3. Copied the new range and pasted into Word (creates a Table)

4. Highlighted the table in Word, then Table > Convert > Table to Text

5. Chose a custom separator of ";"

6. Then Find and Replaced semicolons ";" for a semicolon plus a space "; "

Pretty flawless other than I had c. 150 values and the Table > Text feature only correctly added the semicolons to the first 50 or so, and therefore had to do it in 3 batches. But still, easy peasy

[Hopsta]

add a character to second column for all rows

save file as a CSV

open in notepad and do a search and replace for your ';'

Addresses don't need to be on the same line when putting into outlook

Jobs a gooden

[biturbo]

Last time I did this, if the addresses were in separate rows, you could just copy and paste straight into Outlook with out the ";" needing to be there and then hit the check addresses button....... ;-)

You might also need to find out the max number of email addresses your server handles in one email (our admin had locked ours down to 200)

[RobK]

and put the addresses in the bcc field if you don't want everyone to be able to see all the other recipients' email addresses.