I have the following text from a file (as an example) which is separated with a blank line:



How can I paste this into excel so that all the text separated by a blank line will go into its own cell? I tried using the import text wizard but did not see any options, this is office 2010. Standard paste puts each line in its own cell. Thanks

  • When Excel see a crlf (new line char) cursor goes to cell below, so you need to change the new line characters in a way – Özkan ÖZLÜ Mar 7 '13 at 11:58

You could do this in two steps. First in your text editor, do a search/replace operation where you replace two line endings in a row (ie [LF][LF] or [CR][LF][CR][LF]) with some character that doesn't appear anywhere else in the text. ` for example. Then import the text file as a csv ('comma separated value') file, and change the separator to the character you chose.

(To easily search/replace without needing to know your line ending type, in most text editors you can simply highlight the two lines (from immediately after the 5 in text12345 to the end of the blank line), and copy/paste that into the search box in the search/replace dialog.)

Edit: Here's how to specify a custom delimiter when importing: How do you change default delimiter in the Text Import in Excel?

Also, this question is probably more appropriate to https://superuser.com/ as it's not really programming related.

| improve this answer | |

The following steps will do it if you are happy for text123 to be on the same line as text12345:

  1. Use a 'search and replace' tool (one that supports regular expressions) to replace all double line feeds with a unique token. E.g. \n\n -> XXXXXXXX (or \r\n\r\n -> XXXXXXXX)
  2. Replace all single line feeds with a space character
  3. Replace all unique tokens of step 1. with a single line-feed
  4. Paste into excel.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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