Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Identify Upper Case Entries (2002)

    I have a list of 1300 names. Some are entered all caps and some proper case. Is it possible to automatically determine which ones are all caps?

    Thanks.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify Upper Case Entries (2002)

    Do you want to know in a worksheet formula, or in VBA. And what is your objective, to convert to all upper case, or all Proper case, or do you need to know for another reason?

    If you want to do it in VBA, then you can do something like this:

    <code>
    Public Sub Test()
    Dim bR As Boolean
    If Range("A1").Value = UCase(Range("A1").Value) Then
    MsgBox "Upper Case"
    Else
    MsgBox "Not Upper Case"
    End If
    If Range("A1").Value = Application.WorksheetFunction.Proper(Range("A1").V alue) Then
    MsgBox "Proper Case"
    Else
    MsgBox "Not Proper Case"
    End If
    End Sub
    </code>
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Identify Upper Case Entries (2002)

    ..or futher to legare's suggestion, a function in a module:


    Function Uppercase(strWord As String) As String
    If strWord = UCase(strWord) Then

    Uppercase = "Yes"

    Else
    Uppercase = "No"
    End If

    End Function
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify Upper Case Entries (2002)

    Objective - 1. Identify which ones are in upper case. For example, a1:a1400 has data. In cells b1:b1400 place a u in them if Upper case and nothing if lower case. A worksheet formula or vb is ok, although I sometimes struggle with where to put vb code...

    Thanks.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify Upper Case Entries (2002)

    I non-VBA method to identify the non-proper case would be:
    =CODE(MID(A1,2,1))=CODE(UPPER(MID(A1,2,1)))

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify Upper Case Entries (2002)

    Does not appear to work in all cases. Some names have first, middle and last. Some only first and last... Does this need to be modified to take this into account?

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify Upper Case Entries (2002)

    The following formula will do this if the last character in the string upper or lower case.

    <code>
    =IF(CODE(RIGHT(TRIM(A1),1))=CODE(UPPER(RIGHT(TRIM( A1),1))),"u","")
    </code>


    What do you want if the cell contains something like this:

    MIXed
    Legare Coleman

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Identify Upper Case Entries (2002)

    Or slightly modified:
    <pre>Public Sub Test()
    Dim bR As Boolean
    Dim myC As Range
    For Each myC In Selection
    If myC.Value = UCase(myC.Value) Then
    myC.Offset(0, 1).Value = "Uppercase"
    Else
    myC.Offset(0, 1).Value = "Proper Case"
    End If
    Next myC
    End Sub</pre>


    OR
    <pre>Function CaseType(Test_Text As String) As String
    If Test_Text = UCase(Test_Text) Then
    CaseType = "U"
    Else
    CaseType = ""
    End If
    End Function</pre>


    edited based on latest info!
    Regards,
    Rudi

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify Upper Case Entries (2002)

    Doesn't work for me in XL2K. I don't think worksheet string comparison is case sensative.
    Legare Coleman

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify Upper Case Entries (2002)

    mitjones,
    To whom are you replying?
    The formula I provided compares the second character in the string to its upper case equivalent. It will work as long as the second position is a letter.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify Upper Case Entries (2002)

    Added: forget this reply, it doesn't work.

    Assuming the data start in A1, try

    =A1=UPPER(A1)

    in B1, and fill down. Column B will contain TRUE if the cell in A is all uppercase, FALSE otherwise. If you prefer "u", use

    =IF(A1=UPPER(A1),"u","")

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify Upper Case Entries (2002)

    You're right, of course. A senior moment...

    Here is another VBA function:

    Function IsUpper(varValue) As Boolean
    IsUpper = (varValue = UCase(varValue))
    End Function

    to be used in the form

    =IsUpper(A1)

    or

    =IF(IsUpper(A1),"u","")

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify Upper Case Entries (2002)

    I think that what he is saying is that the string might contain something like this:

    H. Legare Coleman

    or

    H L Coleman

    That's why I used RIGHT instead of MID.
    Legare Coleman

  14. #14
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify Upper Case Entries (2002)

    Rudi, thanks. This did the trick.

    Thanks to all Loungers. This is the best help site ever!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •