Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers

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
add comment

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
add comment
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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