0

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.

Kenny
  • 148
  • 8
  • 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

3 Answers3

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.

Community
  • 1
TheUser1024
  • 2,901
  • 1
  • 17
  • 18
  • `\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
1

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.

hdhondt
  • 4,149
  • 2
  • 14
  • 15
0

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
  • 148
  • 8