Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Remove all words that are not all caps

    Any code out there that would remove all words (as well as objects such as colons or hyphens) in a range that are not all capital letters?
    Thanks in Advance.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I was working on your challenge until I realised that I wasn't sure what, exactly, you wanted.

    Could you post some examples of cell values:- before and after the procedure runs ?

    In case you meant that you wanted to remove cell contents except where all the characters are capital letters, the attached Workbook has a Macro which does that on the Range Samples, which is coloured yellow.
    Attached Files Attached Files
    Last edited by MartinM; 2013-01-31 at 18:01.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    MartinM,
    Took a stab at this from a totally different angle that led nowhere. How simple you make it look. Kudos!
    Last edited by Maudibe; 2013-02-01 at 03:01.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Martin,
    Works perfectly. Thanks. However, the range needs to be more flexible; sometimes it might be A5:A55, another, A7:A90, etc. Any ideas?
    Thanks again.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Here's a simple solution.

    Change the code in the Macro to this (the amendment is in red):

    Code:
    Sub CAPSONLY()
    Dim Strng As String
    Dim Flag As Boolean
    Dim n As Integer
    Dim Character As String
    
    For Each Sample In Selection.Cells
    Strng = Sample.Value
    Flag = False
    Length = Len(Strng)
        For n = 1 To Length
        Character = Mid(Strng, n, 1)
        If Asc(Character) < 65 Or Asc(Character) > 90 Then Flag = True
        Next n
        If Flag = True Then Sample.Value = ""
    Next
    End Sub
    Now the Macro will operate on whatever cells, or range of cells, that you have selected.

    Please bear in mind that actions taken by a Macro cannot be reversed with the Undo command, so be careful !

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Martin, Works great!

Posting Permissions

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