Jump to content

extract partial words from title ?

This topic is 7866 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi everyone:

Here is the challemge I am facing:

I've got titles:

Mr and Mrs John Smith

Capt and Mrs Bill Jones


and I want to end up with

Mr John Smith , Mrs John Smith

Capt Bill Jones, Mrs Bill Jones

Appreciate any suggestions.



Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

This topic is 7866 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.