My guess is that this is a fairly straight forward issue, but I have little (if any) experience with text functions. Any help would be greatly appreciated. I have a license database with a name field (individual) that over the years has had extra data added on many, but not all occurrences. Often the field will contain the company name as well:

john smith / ABC plumbing company

In all occurrences, the individual name is separated from the company name with / -- My goal is to loop through the fields, find the / and everything after and populate a new field, say (companyname)

Then, go through and delete the / and everything after.

Suggestions?

Edited October 31, 201213 yr by Lee Smith
changed text size to readable.

Please attach an actual sample of the text. Often there are keys that we can see to simplify the process.

Here are three examples:

Abner, David / David Abner Electric

Michael Adams / KVK Inc

Asbury, Ed / Larson Const.

Try this (in this order):

name = Substitute ( name ; " / " ; ¶ )

company = GetValue ( name ; 2 )

name = GetValue ( name ; 1 )

Thanks Jeremy - I get the logic of those steps, but do not know enough about scripting to run the data through. Would I create a loop script, and use a setfield script step?

I would create two (stored) calculation fields and check the results. If they're fine, change them into Text fields, and check the results again. Then you can delete the original field (if you're sure the results are fine).

As long as the last name isn't two words such as Van Damme, plus I noticed that there an inconsistency in your data, i.e. the name can be Last first or First first?

The suggestion I made should work to split the person name from the company name whether the person's last name is two words or not, presuming that the users have been reliable about using "/" as the delimiter between name and company data. I don't see where Darren mentioned splitting the name into first and last name. If that's part of the goal, though, it looks like we can tell the difference between "first last" and "last, first" format by the presence of a comma in the name (trapping for exceptions like suffixes). When the format is "last, first", we can use a similar trick to separate them. When the format is "first last," the system would just have to guess, since we can't tell the difference between two-word first names ("Mary Jane") and two-word last names ("Van Damme") based on the content alone. We could make the splitting more intelligent by referencing a corpus of common first and last names, but whether or not that's worth it depends on how much data there is that would have to be verified or corrected by human users after the parsing.

we can't tell the difference between two-word first names ("Mary Jane") and two-word last names ("Van Damme") based on the content alone. We could make the splitting more intelligent by referencing a corpus of common first and last names

Jefferson.

@ Jeremy

The only way that I know of, that does something like this, is an old file from by Lynn Bradford that was originally done in v4 or v5. You can download a copy from this Link

It can probably be improved using today's tools, but I'll let someone else do that.

HTH

Lee

Update:  problem solved using techniques above.  Created new calc field to replace / with return, created two new fields to grab those return seperated values, converted fields to text after verifying calculations returned correct info.

 

Thanks to all!!

 

Darren