Solved

Has anyone already written some version of this Clean up Instr function?

Posted on 2009-06-29
11
226 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Hubbsjp21
  • 6
  • 4
11 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 24737468
you can use strconv()

strconv("RODRIGO ESTEBAN MIGUEL",3)  will give you  Rodrigo Esteban Miguel
0
 

Author Comment

by:Hubbsjp21
ID: 24737564
Hey Cap,

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
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 24737627
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....

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

Open in new window

0
 

Author Comment

by:Hubbsjp21
ID: 24738048
Nice work Mongoose

I have never seen the following before:
-  ReDim
-  Preserve
-  UBound (looked this up)

Everything works except the period.

Thanks - Hubbs
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 24738835
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"
 
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Hubbsjp21
ID: 24738869
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.
0
 

Author Comment

by:Hubbsjp21
ID: 24739014
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
0
 
LVL 10

Assisted Solution

by:therealmongoose
therealmongoose earned 250 total points
ID: 24740002
Hi Hubbs,
 
You are correct it's the last character that wasn't being catered for... Here's an amended version....
 
Cheers,
 
Mongoose
 

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)

    Else

        strWords(UBound(strWords)) = strWords(UBound(strWords)) & "."

    End If

    

    For Each varItem In strWords

    

        strResult = strResult & " " & varItem

        

    Next varItem

    

    strResult = Trim(strResult)

    

    fncProperCase = strResult

    

End Function

Open in new window

0
 

Author Comment

by:Hubbsjp21
ID: 24740006
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


strResult = Trim(strResult)

    

    If InStr(Right(strResult, 2), " ") = 1 Then

    strResult = strResult & "."

    End If

    

    fncProperCase = strResult

    

    

End Function

Open in new window

0
 

Author Comment

by:Hubbsjp21
ID: 24740042
Mongoose - Looks like our comments "crossed in the mail".  I am going to split the points.  Thanks to both of you.

Hubbs
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 24743091
Thanks Hubbs...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now