parseing info

Topic

New Reply

Hellow fellow Accessers!
I am trying to parse some information from a total name field with varying information in it. I have used the instr(), mid(), left(), right() and IIF() to get city, state, zip, country, but the name has me befuddled. The name has varying lengths like this:

John P. Doe
Mr. Johanne P. Doe
Juan Doe
Doe
Juanito Doe, III
Dr. John Citizen
Ms. Juanita Ruiz Cruz

What I would like is to filter the Salutation(if any), FirstName, MiddleInitial (if any), LastName.

Apologize for the long-winded post.
Email response if possible: jason@chitester.com

Thanks in Advance all you ACCESSorizers

Reply | Quote

Replies

Jason: I used this to separate a name field into first and last. Format of [name] is [first last]. You might be able to modify it for your use. Hint: Always keep names separate in your DB. Much easier to work with title, first, mi, last. HTH.

SELECT tbl_Names.Name, InStr([Name],” “) AS [Temp Name], Left([Name],[Temp Name]-1) AS [First Name], Mid([Name],[Temp Name]+1) AS [Last Name] INTO tbl_Split_Names
FROM tbl_Names;

Reply | Quote

BC:
Thanks for the help, however it doesnt solve my problem of the title in the beginning. I guess I will have to do a series of nested IIF’s to first, continue parsing after the first or second space.
I am still looking for more help If anybody has any more suggestions.

Jason Elks

Reply | Quote

I had to do this once many years ago to convert an email address file from an old system to MS Mail (yes, before Exchange and Outlook). It ended up being an incredibly complex VB program that required interaction for every one of the 60,000 name fields. Do you plan to handle names like Jean-Pierre van den Hagen? How about My A? I had one case where the last name was 5 words. I also ran into some very interesting titles and suffixes (Jr., II, 2nd, etc, not to mention mine which is CISSP)

Good luck!

Reply | Quote

Jason:

1st, commiseration. Normalizing names approaches the worst thing you’ll have to do with data – we’ll leave the worst thing unnamed!

As far as the title goes, I have this suggestion. Make a lookup table that has all of the possible titles: “Mr.”, “Mr”, “Mssr”, etc. In a query, pull all the data before the first space in the unnormalized name. Match that against the lookup, table; if it matches, you can write it to a Title field in a resulting table, if it doesn’t, you don’t. No matter how you go about this, there’s bound to be some exceptions that may need manual attention. That’s why they make Credit Managers and A/R staff! (I love that little guy!)

I also saw this add-in featured in the Dec. 2000 issue of Element K’s Inside MS Acces. I’ve not tried it, but you might want to give it a look.

Reply | Quote

You might also want to take a look at ParseRat, which handles names along with other kinds of data. In the meanwhile, you didn’t mention the version of Access you’re using. If it’s 2000, you can use the Split function to parse the words of the name into a single dimension array and then test the elements to see what they are.

Reply | Quote

Thanks for that query…
Instead of going the long, arduous way of arrays, binary coding/testing, which is no doubt the correct way, I decided to query it out

Your script is working and by substituting the criteria, I am getting the desired results.

Seeing that I have to do this over and over, how would you UPDATE a table to include these new fields rather than make a new table?

Reply | Quote

Dear Jason,

Access 97 ver as requested.

Reply | Quote

Dear Jason,

Parsing out data can be difficult especially if there is little or no structure to it. Your best option might be to go for one of the programmes identified by Shane & Charlotte. However if you do want to have a go yourself, this may give you a start.

I once used a dos DB called

Reply | Quote

I got an unrecognized format after unzipping and trying to open it in Access 97. Is this Access 2000 by chance?

Reply | Quote

Thank you all for your help. You have been most helpful. I have the first name and title parsed. I now have to parse the rest. hurumph! I am going to give ruperts functions a try.

jason

Reply | Quote