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

I have this string in an excel file:

4603,2504603#;4616,2504616#;4617,2504617#;4519,2504519#;4620,2504620

(It's actually a lot longer than that, but the pattern is the same).

I need to be able to some how sort this into something that can be read easier. I want the end result to look like this:

4603,2504603
4616,2504616
4617,2504617

I guess I need a program or command that can replace #; with a line break. I need something that will work in Windows 7. I remember doing something similar to this in a Linux class using shell scripting but I can't remember how it was done.

Note: This is NOT homework. It is something my boss has asked me to do at work.

share|improve this question
1  
I was able to paste my data into Microsoft Word, then replace all occurrences of #; with ^p which MS Word interprets as a carriage return (line break). – Kenny Nov 12 '12 at 21:52
    
Post that as an answer if it solved the problem for you. – ChrisF Nov 12 '12 at 22:19

You almost got it mate. Notepad++ can replace stuff with line breaks. Go to the Find&Replace Dialogue and select extended mode. Then you can simply replace all your #; with \r\n.

Thanks to Bob for pointing out that Windows wants carriage-return + line-feed while Unix and other *nixes prefer line-feed only.

share|improve this answer
    
\r\n (carriage return + line feed) is standard and preferred on Windows. A line feed only (\n, usually used by *nix) may not appear properly on other Windows programs. – Bob Nov 12 '12 at 21:42
    
well thank you! corrected that answer. – TheUser1024 Nov 12 '12 at 21:43

If you don't have NotePad++, you still have Word - since you have Excel. Go to Edit > Find & Replace. Enter "#;" in the "Find what" box, and "^p" in "Replace with". In Word ^p means a paragraph marker, i.e a newline or CR/LF.

EDIT

As you're using Excel you can use Data > Text_to_Columns to do the conversion. Select # as the delimiter. As the delimiter cannot be 2 characters (#;) you will also have to use Edit > Find & Replace to remove the semi-colons (;) afterwards.

And, of course, Excel will play havoc with the commas as it treats them as thousands-separators.

share|improve this answer
up vote 0 down vote accepted

I was able to paste my data into Microsoft Word, then replace all occurrences of #; with ^p which MS Word interprets as a carriage return (line break).

share|improve this answer

You must log in to answer this question.

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