asked on

Data Parsing - Removing a string and replacing it with a carriage return

I have a document with approximately 4,000 email addresses that need to be parsed into a single column by having the ";" (semicolon) string removed and replaced with a carriage return. Can anyone recommend a PERL or VBA snippet that can do this in fairly short order. Maybe theres an advanced text editor that someone could recommend that could do the same.

INPUT STRING

blahblah1@parsemeplease.com; blahblah2@parsemeplease.com; blahblah3@parsemeplease.com; blahblah3@parsemeplease.com;

DESIRED OUTPUT

blahblah1@parsemeplease.com
blahblah2@parsemeplease.com
blahblah3@parsemeplease.com
blahblah3@parsemeplease.com

Any help is greatly appreciated :)

GrahamSkan

MyNewString = Replace(MyString, ";", vbCR)

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pweegar

I like textpad. Simply do a search for ; (colon space if you have spaces after the colon). Replace with \n. It's fast!

Mike Tomlinson

Using a VBScript (.vbs) file:

Option Explicit

Const ForReading = 1, ForWriting = 2

Dim fileName, fso, f, lines

fileName = "c:\someFile.txt"

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(fileName) Then
Set f = fso.OpenTextFile(fileName, ForReading, False)
If Not f.AtEndOfStream Then
lines = f.ReadAll
f.Close

lines = Replace(lines, "; ", vbCrLf)

Set f = fso.OpenTextFile(fileName, ForWriting, True)
f.Write(lines)
f.Close

MsgBox fileName, vbOKOnly, "Changes Made"
Else
MsgBox fileName, vbOKOnly, "File Is Empty"
End If
Else
MsgBox fileName, vbOKOnly, "File Not Found"
End If

nanohurtz

ASKER

...and there you have it, a solution right under my nose and no need for scripting. Thanks a million Graham.

GrahamSkan

Thanks nanohurtz.

Don't feel bad. The answer is always obvious afterwards. It took me two goes.

cheers, graham