Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a column of cells in excel which contain mails, some only contain one mail as they should but others, have more than one email per cell in this format:

one@example.com, two@example.com, three@example.com

What I want to achieve is getting them like this:

one@example.com
two@example.com
three@example.com

I´m guessing (don´t take my word on it) you could do some kind of if statement along the lines of

if (cell contains ", ")
get string from ", " to ", " and paste somehow
else "b1"

You may be screaming at your screen right now :) (or laughing) but it´s just how would approach it, no idea of the functions to be used or if it is even possible this way.

So if you have any ideas I appreciate it!!

Just in case, I want to do a csv out of this file so, any workaround that would get this done would do...

Thanks in advance!

Trufa

BTW I hope I have explained the problem clear enough if not please ask for clarifications!

EDIT: The problem is actually solved, I gave in and did it one by one, the one method that never fails you :) Iwpuld appreciate any ideas anyway for the future and for knowledge sake!

share|improve this question

4 Answers 4

Within Excel, try Data -> Text to Columns.

Then choose "," as the delimiter. This will put column breaks where the commas are now.

share|improve this answer
    
I´ll try! thanks –  Trufa Oct 22 '10 at 19:21
    
I conuldt find a way to do that! thanks anyway look at my edit please :) –  Trufa Oct 23 '10 at 1:30

Export your Excel file to a csv.

Copy it across to a Linux machine and edit it in Vim editor.

Then type:

%s/,/\\n/g

This is the breakdown of that command:

  • : - run command
  • %s - substitute
  • / - separator
  • , - string to find
  • / - separator
  • ** - a control character to ignore the next character, but in this case we want the following \n part to be the string it replaces the comma with
  • \n - string to replace comma with
  • / - separator
  • g - globally

This will find and replace the commas with a line break globally and put every cell onto a new line. You can then reimport this file into Excel.

Apologies if you dont use Linux - that is my speciality and the editor and string manipulation tools are much better than on Windows.

share|improve this answer
    
Notepad++ also handles regex search and replace, and runs on windows. I use it regularly for cases like this. –  glallen Jun 20 '12 at 16:22

Can't you just save as a CSV and do a search and replace for , to \n?

share|improve this answer
    
So, \n is valid in a csv file, it will undestand? –  Trufa Oct 22 '10 at 18:17
    
@Trufa: It's not; I used \n as a placeholder for a line break in Notepad++. You'll have to find out what a line break is represented as in search and replace in your text editor. –  Hello71 Oct 22 '10 at 18:31
    
@I´m on it thanks! –  Trufa Oct 22 '10 at 18:37
    
I conuldt find a way to do that! thanks anyway look at my edit please :) –  Trufa Oct 23 '10 at 1:29

I'm not sure if my answer fits in here since it's more of a Stackoverflow answer, but you could write a macro to do it fairly easily. For example, the following macro would assume that the current email addresses are in the first column and would copy them to the second column:

Cells(1, 1).Select
Dim curr As String
Dim cnt As Integer
cnt = 0
For i = 1 To ActiveCell.SpecialCells(xlLastCell).Row
    curr = Cells(i, 1).Value
    If InStr(curr, "@") Then
        If InStr(curr, ",") Then
            Dim tmp() As String
            tmp = Split(curr, ",")
            For j = LBound(tmp) To UBound(tmp)
                cnt = cnt + 1
                Cells(cnt, 2).Value = tmp(j)
            Next
        Else
            cnt = cnt + 1
            Cells(cnt, 2).Value = curr
        End If
    End If
Next i

I don't know VBA that much though, so might be better ways of doing it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.