Jack Posted November 5, 2002 Share Posted November 5, 2002 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 Link to comment Share on other sites More sharing options...
spb Posted November 5, 2002 Share Posted November 5, 2002 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 More sharing options...
kennedy Posted November 6, 2002 Share Posted November 6, 2002 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 More sharing options...
Jack Posted November 6, 2002 Author Share Posted November 6, 2002 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7969 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 accountSign in
Already have an account? Sign in here.
Sign In Now