extract partial words from title ?

[Jack]

Hi everyone:

Here is the challemge I am facing:

I've got titles:

Mr and Mrs John Smith

Capt and Mrs Bill Jones

etc

and I want to end up with

Mr John Smith , Mrs John Smith

Capt Bill Jones, Mrs Bill Jones

Appreciate any suggestions.

Thanks.

-jack

[spb]

Do you want "Mr John Smith, Mrs John Smith" to be the contents of one field in one record? Or do you want to end up with separate records for the two Smiths?

If the former, there are various ways of rearranging the data, but they all depend a lot on the consistancy of the incoming data. for example, is the word "and" present in every record, or do some of them use "&"? What do the records of an unmarried Mr John Smith look like? I'd probably start with a found set of all records containing " and " (include leading & trailing space between the quotes to avoid finding Mr. Pete Grand). This will probably be thee bulk of them & you can set up a calc field that will unscramble the names. Then deal with the special cases individually.

If the latter, you'd have to set up a global field to temporarily store the name. Make a script that puts the name in this field, overwrites the data in the real field to the immediate right of the first title with the global, grabs the second title and sticks it in front of the contents of the global, makes a new record, sets the new name field to the global. You could loop that to run through all your data.

Steve Brown

[kennedy]

Is the field *always* of exactly this format:

<titleA> and <titleB> <name>

where titleA and titleB are *always* exactly one word (never two and never zero).

If so, its easy:

NameA = LeftWords(Input, 1) & " " & RightWords(Input, WordCount(Input) - 3)

NameB = RightWords(Input, WordCount(Input) - 2)

If not, then you need to describe a lot more about the content of the input field.

[Jack]

Yes, the format is always the same and the titles are always seperated by "and".

(Only one record is different).

So it *should* be any easy matter to extract what I need.

Kennedy, I think your method seems easier, but I'll probably try Steve's first for the learning experience.

Thank you both.

-jack