How to manipulate this string of text to make it readable?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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).