Excel Question - converting text files

I have a text file with about 1000 email addresses that are semi-colon delimited with no carriage returns. (Don’t worry, I am not going to spam anyone). I need to import these into an Excel file so that the addresses are listed one to a row. Excel is looking at this as a list of data and wants to import it along the columns. If I had fewer items in the list this wouldn’t be that big of an issue but I have more items than there are columns.

How can I get this into a reasonable format in Excel withour going into the text file and hitting the carriage return 1000 times.


If you can load them in separate cells in one row, then you can transpose that to a column. Select the cells in the row and do an edit/copy. Then put select the cell you want to be the top of the column and do edit/paste special. The Paste Special window has a checkbox for “Transpose”, which will convert your row into a column (or vice versa).

Alternatively, any decent text editor will have a search-and-replace function which would allow you to replace the semicolons with paragraph marks. Different editors handle control codes differently, so you’ll have to RTM to see how you tell it the replacement should be a carriage-return/linefeed.

How are the individual items within each row separated? If you’ve got tabs or commas between the items and a semicolon indicating the end of the row, you have it made.

Open up a blank Word document and paste your data. Highlight the data and convert it to a table. Once you’ve worked out the kinks that will usually occur if one or two items is missing in a row, you can import this over to Excel.

If your list just uses blank spaces in between row items, you’re screwed. Get another extract if you can.

Maybe I’m misunderstanding the question, but it sounds to me like the best thing to do is to open the text file in word, and use replace to convert all the semi-colons into carriage returns. In word, that’s ^p unless I’m totally mistaken. Of course, micco already said substantially the same thing, but that’s never stopped me before…

That did it. I was trying to do the same thing but I didn’t know about the ^p = carriage return trick. Thanks very much.