Has anyone already written some version of this Clean up Instr function?
Posted on 2009-06-29
I am cleaning data right now, and I am starting with First and last names. Our first name field may have as many as 3 - 5 "First names" in it. The "Last Name" also contains multiple and or hyphenated names, and suffixes as well. My plan is to make sure that the capitalization is correct. I am using the InStr function to find spaces, and correct upper and lower case based on the spaces. I started by putting some code in a query field, but the code gets real long real fast, depending on how many spaces you account for. I quickly realized this would be better accomplished with a function. Below is an example of what I want to clean:
First Name: RODRIGO ESTEBAN MIGUEL s/b Rodrigo Esteban Miguel
Last Name: Santos- JIMENEZ, JR. s/b Santos-Jimenez Jr.
I figured someone must have already written something similar to what I am looking for. If not, any suggestions on the cleanest way to do this?
Thanks - Hubbs