How can I build an expression to extract text in two different formats?

[cliff.clayman]

Yes, it is possible to have an organization with a comma in the name. I missed that in the sample data. ABC, Inc. does exist is the data. Also, in the macro:

Set db = CurrentDb
Set rs = db.OpenRecordset("FM0034")
Do While Not rs.EOF
nm = rs!Insured_Name <------ Do I have to explicitly name the field name here? Will it only work for the one field I define?

[June7]

The comma was assumed to be the 'tell' that the value was a person's name. The code would have to be modified to look for other indicators of an organization, such as "Inc", "LLC", "DBA", "Corp", etc. Maybe "Inc" is the only one with a comma, if so, the code modification is relatively simple.

Orange's code uses specific field in the Sub. The Function does not - the field is specified in the Function call.

Why are you using the Sub?

[cliff.clayman]

I'm not. I was just making sure I didn't need to alter it.

[June7]

Issue resolved?

[cliff.clayman]

Not quite. I still have an issue with the corporation/company names. The code currently takes out a comma if there is one and then the match fails. I have some corporation/company names like 'ABC Inc.' and some like 'ABC, Inc.' I also have some suffixes that are splitting the names incorrectly as well. Joe Johnson, DDS becomes DDS Joe Johnson and fails the match. I need some way to not reformat those type.

[June7]

That means more conditional statements in the code to test for each possible situation. This is why parsing string data gets very unwieldy. Consistency in structure is critical. Otherwise you face the GIGO syndrome (Garbage In Garbage Out).

[cliff.clayman]

Yeah, unfortunately I have no ability to change the data that is coming in. It is a weak front-end that allows all these different formats to come through. Things like this are not thought about when it comes to reporting and then analysts like me are stuck trying to work around it.

[cliff.clayman]

The function fails when I have a name like this:

Walter J Jackson, DDS, MS, PA

How can the function be modified to accommodate for this?