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 group of names and addresses that I would like to import into Outlook.

At the moment I have imported them into Excel, but all names and addresses are in one long entry.

All are already separated by a comma.

How can I get Excel to select each "value" and move it to a separate cell?

Edit: I had already tried taking a text file and saving it as a .csv file. However, all contacts load into a single cell.

I am using Excel 2003.

Thanks.

share|improve this question
    
Which version of Excel are you using? –  BloodPhilia Jun 16 '10 at 15:59
    
2003, thanks ................ –  Xavierjazz Jun 16 '10 at 19:36
    
is it solved now? –  BloodPhilia Jun 16 '10 at 19:44
    
@BloodPhilia - as I have commented under Michael's answer, it works, but I need it to split the data into rows, not columns, as there is more data than columns. As a work around, I can split the file into 2, but it would be great to have a way to have the program change them from columns to rows. I'm looking. Thanks –  Xavierjazz Jun 16 '10 at 20:01
add comment

5 Answers

up vote 2 down vote accepted

What you need isn't Excel. Excel and Calc (OO.o) can both open CSV as spreadsheets, but what you want is something to manipulate the text file into a properly-formatted CSV file.

For this you want a good text editor. There are very few text editors that allow you to do multiline search and replace (don't know why, as this is an incredibly useful feature and easy to implement). But a very good free editor that (sorta) has this functionality is Notepad2. It is basically an upgraded version of Windows' default Notepad, but not quite as bulky as Notepad++. You can replace Windows Notepad with Notepad2 or Notepad++, but that's beyond the scope of this answer.

After installing Notepad2:

  1. Open up the text file and hit Ctrl + H to bring up the find & replace dialog.
  2. Type a comma or comma and space into the Search String box.
  3. Now you'll notice that the Replace with: box is only a single-line. That's OK because Notepad2 supports regex right out of the box. So check the box that says Regular expression search.
  4. Despite what the above checkbox reads, it's actually Regular expression search AND replace—meaning that you can use regexps in the Replace box as well. So in it, type \n and hit Replace All.
  5. Then just save as *.csv and quit. If you open it up in Excel/Calc, it should now be a column of names/e-mails.

Edit:

Actually, re-reading your question, it occurs to me that you may want 2 columns: 1 for email, one for name. If that's the case, then it's a little more difficult to do than just replacing all commas with line-breaks. But fear not! Regexps are here to help once more:

  1. Open up the original text file.
  2. Pull up the search & replace dialog.
  3. Type this into the search box: \([^,]+\),\([^,]+\),
  4. Type this into the replace box: \1,\2\n
  5. Hit Replace All.
  6. Save as *.csv and quit.

You may optionally modify the regexps to put a space after the commas if that is how the text file is formatted or how you want it formatted, e.g.

first last, user@domain.com, bob builder, bob@builder.com, foo...
versus:
first last,user@domain.com,bob builder,bob@builder.com,foo...

Edit2:

For the Notepad++ flavor of regexp, you don't need to escape groupings with backslashes, so the corresponding search regexp would be:

([^,]+),([^,]+),

If there are empty email addresses:

([^,]+),([^,]*),     // assuming that the email is the second parameter
([^,]*),([^,]+),     // make the first parameter optional

Edit3:

To deal with completely missing fields, it gets a little messy. This is because neither Notepad2 nor Notepad++ support optional groups or negative lookbehinds. So we need to apply 2 different regexps:

  1. This takes all consecutive e-mails and injects an empty field (, ,) between them. Run this (with Replace All) multiple times until you stop getting matches.:

    ([^,]+@[^,]+), ([^,]+@[^,]+),*   // search for
    \1, , \2,                        // replace
    
  2. Manually add the extra empty field at beginning or end of document if needed.

  3. Depending on whether the e-mail comes first or name comes first, replace either of these:

    ([^,]+), ([^,]*),\s     // email first
    ([^,]*), ([^,]+),\s     // name first
    

    with:

    \1, \2\n
    

And that should do it. The \s above is just a space character. You can either put a space there or write \s in the regexp.

Note: I normally try to avoid asking a person to install a new text/graphics/audio/video editor just to solve a specific problem, but this is one instance where: 1.) it's a very lightweight (~300KB) free program that I know works; 2.) it's incredibly useful and almost anyone who hasn't already installed a notepad replacement like Notepad2/Notepad++ would benefit from installing it. In fact, I install it on every Windows computer I use, and also every Windows workstation at my work.

share|improve this answer
    
Thanks. The 2nd operation you described would not work, but the first one worked like a charm. Perhaps the 2nd operation failed because not all addresses have names attached??? I have Notepad++ installed and used it. –  Xavierjazz Jun 17 '10 at 15:06
    
I haven't tested it on Notepad++ (I use N++ at work, but only N2 at home), so I don't know if they parse RegExps the same. But, yea, the way I wrote it, you need to have both. How are the addressless items formatted? Do they have an empty item after them where the email would go, like: ..., grandpa, , grandma... or is it just ...grandpa,grandma...? –  Lèse majesté Jun 17 '10 at 15:21
    
Here's a small example: alxxxc@yahoo.ca, info@myxxxxub.ca, music@xxxxhat.com, –  Xavierjazz Jun 17 '10 at 15:54
    
Here's a better example: music@xxxhat.com, Axxx xx Cxxxx <axxx@microsoft.com>, –  Xavierjazz Jun 17 '10 at 16:12
    
I haven't tried it with display names, but as long as the spacing is as you've written, and only the names are ever missing (there aren't any names that don't have addresses), then the 3rd edit should work. –  Lèse majesté Jun 17 '10 at 17:03
add comment

CSV is nothing more than a fancy way of saying, "plain text separated by commas," hence the name Comma Separated Values.

To convert the file to CSV format change its extension from .txt to .csv.

Excel knows that files with the .csv extension are, well, comma-separated-values, and it will put each value into its own cell.

For instance, I started out with this file:

a,1,bla,10
2,r,sasdfa,1as0

I saved it as bla.csv and opened it in Excel. This is what it looks like:

example of CSV file

My installation happens to be new, and I made no changes to Excel's setup to open CSV files automatically.

share|improve this answer
    
-1 Not really an answer to the question... –  BloodPhilia Jun 16 '10 at 18:09
1  
@BloodPhilia: only if the question itself is wrong. The point is, judging from the OP's description, he already has a CSV file. Now, if he were asking how to convert it to XLS, you'd have a point, but he's asking how to convert comma-separated-values to CSV. –  Nathan Fellman Jun 16 '10 at 18:13
    
The OP is asking: "How can I get Excel to select each "value" and move it to a separate cell?" That would be unthreading the CSV and converting it to xls no? –  BloodPhilia Jun 16 '10 at 18:15
1  
well, if the OP renames the file so that it has a .csv extension, excel will do what he wants automatically. –  Nathan Fellman Jun 16 '10 at 19:10
    
@ Nathan Fellman - this does not work. All the names are in one cell. –  Xavierjazz Jun 16 '10 at 19:24
show 7 more comments

For what it's worth, in Excel 2003 the tool is still under the Data menu but is called "Text to Columns" and allows you to select the delimiting character.

share|improve this answer
    
+1 Thanks for the addition. –  BloodPhilia Jun 16 '10 at 17:34
    
Plus one, thanks. –  Xavierjazz Jun 16 '10 at 19:41
    
This works, however, it splits into columns. I get a message that some data will be lost as there is more data than columns. Is there a way to have it spit out rows rather than columns? –  Xavierjazz Jun 16 '10 at 19:45
    
There should be an option to not omit that information. –  BloodPhilia Jun 16 '10 at 19:54
add comment

On Excel 2007, select the first cell, go to the data tab and click Convert text to table. A window pops up, select delimited and click next. Now select the right delimiter (comma in your case) and click next. Click finish.

Check here also for a more eleborate explanation: https://ask.qut.edu.au/app/answers/detail/a_id/2076/~/how-to%3A-split-delimited-text-into-multiple-cells-in-excel-2007.

share|improve this answer
    
Plus one, thanks –  Xavierjazz Jun 16 '10 at 19:41
add comment

I wrote a little PHP-script for you, enter the single "," delimited line into here and click submit. Select and copy the list and paste it into the first cell of excel.

share|improve this answer
    
This is not a real answer, as there's no explanation to HOW the problem can be solved. You could however mention this as a comment to your previous answer. –  TFM Jun 16 '10 at 22:07
    
That's a different answer, Excel just does not have the posibility to convert columns to rows, it can only convert csv records to rows. –  BloodPhilia Jun 17 '10 at 8:31
add comment

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.