0

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

text123
text12345

text123456
text1234567

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

migrated from stackoverflow.com Mar 8 '13 at 8:58

This question came from our site for professional and enthusiast programmers.

  • 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
1

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.

0

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.

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.