asked on

Find/Replace Partial Field

I am dealing with a Corel Paradox database which has roughly 4500 records. I have Fname, Lname, Spouse, etc fields which are then combined into FullName fields. A lot of these FullName fields have a Mr. & Mrs. in front or something like that. I cannot seem to figure out how to find and delete just the Mr. & Mrs. out of that field. Any help would be greatly appreciated.

redrumkev

paulkc,

How are you doing?

Are you able to export this data to another data source [something such as .xls (Excel) or .csv (comma delimited, also can be opened in Excel)]. If so, the solution in Excel is pretty straight forward, using any one of the following resources:

http://en.allexperts.com/q/Excel-1059/2008/11/Find-Replace-Excel.htm

Or use the "Convert Text to Columns Wizard", where you can select the columns and the delimiter. In your case, it is probably a space, so it will take something such as:

Mr. Expert Exchange, in one cell and break it up into
Mr.
Expert
Exchange

On the same row, but each in a different column. Then you can combine the names, using the concatenate formula, to end up with:

Expert Exchange

In one cell. That will give you your list of 4500 first and last names... Then just import the list back into the database.

Let me know if this is a possiblity for you...

Hope this helps,
Kevin

Paul Cahoon

ASKER

That might be possible but I was hoping to do it within Paradox if possible. This can be an ongoing problem due to some issues in the whole program (I didn't write it). If it is too difficult, then I will look at the export/import method.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

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

Start Free Trial

Paul Cahoon

ASKER

Ok, that works, but how would I make this to also remove "Mr. & Mrs." at the beginning. Also, I have ended up with a single space at the beginning of some of these names which is causing the names to be out of line when printed on a report. Can I use this to solve that too?

Mark Wills

the Ltrim() should have fixed leading spaces - might be a tab character ? Maybe that is why it still exists in front for some of them... Or just increase the first digit if the title is always followed by something you dont want (but must be always), and the 30 should really be the length of the field (you may have gathered that already).

so your calculated field now has the formula :

iif( name.search("Mr.") > 0, ltrim( name.substr(4,30)), iif(name.search("Mrs.") > 0, ltrim(name.substr(5,30)), iif(name.search("Mr. & Mrs.") > 0, ltrim(name.substr(11,30)), Name) ) )

The format of the iif() statement is :

iif( <expression>, <value if true>, <value if false>)

and as we are doing you can next them...

iif( <expression>, <value if true>, iif( <expression>, <value if true>, <value if false>) )

Just have to be vary careful about brackets and always best to start small, save, and then add the next bit (then save, and then add the next bit)...

Paul Cahoon

ASKER

Ok, I will do some playing with this. It's very similar to using the IF function in Excel. As for the spaces I was discussing, this is not an issue with records that have Mr., Mrs., etc. This is records that the secretary has gone in and removed the Mr., Mrs., etc but forgot to remove the extra space that was between them. Is there a way to search only the first character and if it is a space then use the ltrim function?

Mark Wills

Yep, at the very end - the final <value if false> can be encapsulated with the ltrim() function :

iif( name.search("Mr.") > 0, ltrim( name.substr(4,30)), iif(name.search("Mrs.") > 0, ltrim(name.substr(5,30)), iif(name.search("Mr. & Mrs.") > 0, ltrim(name.substr(11,30)), ltrim(Name) ) ) )

Paul Cahoon

ASKER

Ok, I just thought about the fact that using this method, I would have to change every report in my software (probably well over 100) in order to use this method. I think I like Kevin's idea above of exporting it to Excel and back in. I did some experimenting with this but I cannot seem to get it to work properly. I got it exported just fine and was able to edit the fields easily using the replace function as I am much more proficient in excel than I am in paradox. My problem is getting the data back into paradox. I can get it imported back in by overwriting the table but I loose my whole table structure doing this. This table has well over 100 fields so I really don't want to have to restructure it? Is there a better way?

Paul Cahoon

ASKER

Did I lose everyone on this? I really would like to get this solved if possible. The software developer wants a fee to do this and I am trying to avoid this if possible. As I said, I can get it into Excel and get the data manipulated as I want it. I just can't get it back into the Paradox table in the proper format.

Mark Wills

No, you won't be able to get it back in easily (well, that does depend on the tables)...

If you have the developer edition, you could create a blank form. put a button on it, and on the pushbutton write some code to open a TCursor and update the table directly. It is also possible to write some script and execute almost identical code.

But where is the data coming from ? That is where you need to intercept and make sure it doesn't get entered into the name itself - rather a seperate field (like 'Title').

Otherwise if the point of origin is not fixed then you will continue to run into problems and end up having to do a runtime type fix anyway (such as the report).

Paul Cahoon

ASKER

I see your point. The fullname field is some sort of calculated field using several fields: title, firstname, lastname, spouse, etc.