Hubbsjp21
asked on
Has anyone already written some version of this Clean up Instr function?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You learn something every day... Just spent 15 minutes writing a function only to see cap's post.... every day's a school day. The below will however identify a single letter in a string and add a "." after it....
Watch out for rouge names though - e.g. MacKenzie etc....
Watch out for rouge names though - e.g. MacKenzie etc....
Option Compare Database
Option Explicit
Function fncProperCase(strInString As String) As String
Dim strWords() As String
Dim strResult As String
Dim x As Integer
Dim varItem As Variant
ReDim strWords(0)
strInString = LCase(Trim(strInString))
For x = 1 To Len(strInString)
If Mid(strInString, x, 1) = " " Then
strWords(UBound(strWords)) = UCase(Mid(strInString, 1, 1))
If x > 1 Then
strWords(UBound(strWords)) = Trim(strWords(UBound(strWords)) & Mid(Trim(strInString), 2, x - 2))
End If
If Len(strWords(UBound(strWords))) = 1 Then strWords(UBound(strWords)) = strWords(UBound(strWords)) & "."
strInString = Trim(Right(strInString, Len(Trim(strInString)) - x))
ReDim Preserve strWords(UBound(strWords) + 1)
x = 1
End If
Next x
strInString = Trim(strInString)
strWords(UBound(strWords)) = UCase(Mid(strInString, 1, 1))
If Len(strInString) > 1 Then
strWords(UBound(strWords)) = strWords(UBound(strWords)) & Mid(Trim(strInString), 2, Len(Trim(strInString)) - 1)
End If
For Each varItem In strWords
strResult = strResult & " " & varItem
Next varItem
strResult = Trim(strResult)
fncProperCase = strResult
End Function
ASKER
Nice work Mongoose
I have never seen the following before:
- ReDim
- Preserve
- UBound (looked this up)
Everything works except the period.
Thanks - Hubbs
I have never seen the following before:
- ReDim
- Preserve
- UBound (looked this up)
Everything works except the period.
Thanks - Hubbs
redim preserve & ubound are used in array structures - useful if you need a holder for several values of the same type...
The code should work if there's a single letter in the string supplied - can you post an example of a data string that doesn't work...
I did thest the code with "THIS IS A TEST" and got "This Is A. Test"
The code should work if there's a single letter in the string supplied - can you post an example of a data string that doesn't work...
I did thest the code with "THIS IS A TEST" and got "This Is A. Test"
ASKER
If you can very quickly, test it with "THIS Is A" and see if you get the correct result. I think it may be because the single letter is the last letter. In the mean time, I will set up the data to show you.
ASKER
Hey Mongoose,
I just tested "THIS IS A TEST" and "THIS IS A" in the immediate window, and the results were:
"THIS IS A TEST" - This Is A. Test (works fine)
"THIS IS A" - This Is A (no period)
I appears by my limited analysis that it has something to do with the single character also being the last character. Unfortunately, that is usually going to be the case for my purposes. I have no idea how to fix it because I still am not even 50% sure how it works based on the functions(that are new to me) you used.
Hubbs
I just tested "THIS IS A TEST" and "THIS IS A" in the immediate window, and the results were:
"THIS IS A TEST" - This Is A. Test (works fine)
"THIS IS A" - This Is A (no period)
I appears by my limited analysis that it has something to do with the single character also being the last character. Unfortunately, that is usually going to be the case for my purposes. I have no idea how to fix it because I still am not even 50% sure how it works based on the functions(that are new to me) you used.
Hubbs
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Mongoose,
I added the "If" clause below on the attached snippet in order to get the period at the end when necessary. If you feel like making an adjustment to the code you sent to account for the single character being the last character, great. If not, I am good to go.
Thanks for your snippet and input.
Hubbs
I added the "If" clause below on the attached snippet in order to get the period at the end when necessary. If you feel like making an adjustment to the code you sent to account for the single character being the last character, great. If not, I am good to go.
Thanks for your snippet and input.
Hubbs
strResult = Trim(strResult)
If InStr(Right(strResult, 2), " ") = 1 Then
strResult = strResult & "."
End If
fncProperCase = strResult
End Function
ASKER
Mongoose - Looks like our comments "crossed in the mail". I am going to split the points. Thanks to both of you.
Hubbs
Hubbs
Thanks Hubbs...
ASKER
I guess I didn't look very hard for that function, 'cause I saw it and didn't read it. Since that was so easy, can you suggest a way to add a period after any one character string withing the field?
i.e. Gerald S becomes Gerald S.
or J D becomes J. D.
Thanks - Hubbs