Selective truncating of text fields

[agray123]

I have a really messy data set I am trying to clean up in Crystal before moving it into Peoplesoft

My data in the field in question contains the manager name, some employees typed this directly in

Manager
John Doe
Jane Doe

While others selected a lotus Notes address book value that came across like this:
CN=Thomas Vera/O=PHEAA

And some are blank....

How do I get the CN+ and /O=PHEAA out without messing with the other fields?

 

[synapsevampire]

Using Crystal as a data cleansing tool is a unique concept...

Anyway, you can remove text by using a formula with the replace function, as in:

replace(Replace ({table.field}, "CN=", "&quot,"/O=PHEAA","&quot

-k

 

[MJRBIM]

Name : {@TRIM GARBAGE}

Formula :

IF Left ({Table.NAME},3 )= "CN=" THEN Replace (Replace ({Table.NAME},"CN=" ,"" ),"/O=PHEAA" ,"" )
ELSE {Trim_txt.NAME}

 

[synapsevampire]

Or if the text varies, but you always have the = and /, use:

mid({table.field},instr({table.field},"=&quot+1,
instr({table.field},"/&quot-instr({table.field},"=&quot-1)

-k

 

[agray123]

Thanks to both of you. I used each formula and had a competition, and you both tied.

I am having to use Crystal to clean up this mess because I do not have access to anything else. And I always want to learn something new about the software

 

[synapsevampire]

Even something like Access would be a better tool for cleaning data, and the database that you're moving it from probably has a SQL language.

Anyway, good luck with it.

-k

 

[agray123]

I am moving it from an excel file generated by Lotus Notes...actually to an access database. I just am more comfortable with crystal although I am mucking around a bit with access.