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.
Best Answer
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:
Ctrl + H
to bring up the find & replace dialog.Search String
box.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 saysRegular expression search
.\n
and hitReplace All
.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,\2\n
Replace All
.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.
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:
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:
This takes all consecutive e-mails and injects an empty field (
, ,
) between them. Run this (withReplace All
) multiple times until you stop getting matches.:Manually add the extra empty field at beginning or end of document if needed.
Depending on whether the e-mail comes first or name comes first, replace either of these:
with:
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.