Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there any simple method that doesn't resort to macros whereby a plain text file or string can be imported into excel without using a line break as a row delimiter?



A string in this format, and with approximately one million rows and 20 columns is being returned by an application. I need the simplest way to load this into excel.

Solution 1: Import with ; as the delimiter, paste transpose the columns, and then use 'text to columns' feature to convert all the comma delimited cells in the one column into many columns. This doesn't work because excel only supports a little over 16,000 columns, and we have many more rows than that.

Solution 2: Load the string into an advanced text editor that supports replacing the ; with a line break. (Most simple text editors only support simple text replace, no line breaks.) I would have trouble instructing a business user how to do this, and am not aware of any such tools that they are likely to have on their windows machine.

Can anyone else propose a simple way to deal with this miserable data format? As far as I know, there's no way to specify a row delimiter other than line break when importing into excel.

share|improve this question
up vote 1 down vote accepted

Use Microsoft Word as per your solution 2, it's probably already in your business users PC. The newline is indicated as ^l. See also

share|improve this answer

Notepad++ can be used with solution 2 rather easily. In addition to plain text find and replace, it can also use escape strings (such as \t for tab, \n for new line), and can also do regex replacements.

You'd want to do something similar to the following:Notepad++ find and replace dialog

share|improve this answer
Not a bad program for the average Joe to have either. – Alain May 5 '11 at 23:39
Addition: Microsoft Word can find and replace ; with ^p to create line breaks. – Engineer Toast Jun 30 '15 at 22:48

You can use "Other" delimiter with value "0010". This will not be visible on the screen but works and splits the new line separated values of a cell in text to column use.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .