Results 1 to 6 of 6
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Strip out punctuation - Excel 2003

    This is for the same project as 'Letters to Individual Cells', posted several days ago. I'm creating simple cryptograms, and my process currently uses Word, Excel, and an encrypting program from outside. I'm trying to integrate things as much as I can. Here's what I need to do now:

    Cell A1 contains a sentence that consists of UPPER CASE LETTERS, Spaces, and various punctuation marks.

    I want to duplicate the sentence, but stripped of the punctuation marks, in Cell B1. (In other words, I want to strip out anything that isn't a letter or a Space.) I'm stripping them manually right now, and of course I sometimes overlook something.

    It would be nice, but is not required, if there were something that forced any lower case letters in A1 to become UPPER CASE in B1.

    I will need to copy the stuff in B1 to my clipboard, then paste it as text into the encrypting program.

    This was easy in BASIC many years ago, but I no longer remember much BASIC, and I don't speak the loops and string processing parts of VBA.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is function I created long ago to make all uppercase and strip everything by text and numbers. If you don't want the numbers don't include the red text

    Code:
    Option Explicit
    Function UStrip(sWord As String) As String
    'This function takes a string outputs an uppercase equivalent with
    '  all characters stripped but numbers and letters
    'Uppercase is used since it is a better function with "Find" since case becomes immaterial
        Dim x As Long
        Dim sCharacter As String
        sWord = UCase(sWord)  'Convert to Uppercase
        UStrip = "" 'Set originall as null
        For x = 1 To Len(sWord) 'Act on each letter
            sCharacter = Mid(sWord, x, 1)
            If Asc(sCharacter) >= 65 And _
                Asc(sCharacter) <= 90 Then '65-90 are ASCII "A"-"Z"
                'sCharacter is a LETTER
                UStrip = UStrip & sCharacter
            ElseIf sCharacter = " " Then
                'sCharacter is a space
                UStrip = UStrip & sCharacter
            ElseIf Asc(sCharacter) >= 48 And _
                Asc(sCharacter) <= 57 Then '48-57 are ASCII "0"-"9"
                'sCharacter is a "text-NUMBER"
                UStrip = UStrip & sCharacter
          End If
        Next x
    End Function

    Add it to a module then use something like:
    =ustrip(a1)

    Steve
    PS the blue lines are to keep spaces. In my original code I wanted to eliminate spaces as well...
    Last edited by sdckapr; 2011-07-28 at 19:06. Reason: Added PS and modified code. Forgot about desire to keep spaces...

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Lou Sander (2011-07-28)

  4. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    This is most excellent! It sure brings back memories of BASIC!

    As I think about my overall project, it seems as though it might be simple to do everything with one Excel function. Here's what's needed:

    I start with a sentence that contains only UPPER CASE letters, Spaces, and punctuation. No numbers. (I'd need to start with different sentences each time I used the function. Whatever I want to encrypt, I'll put in cell A1).

    Somewhere I have a substitution table that contains multiple ciphers (we don't want to encrypt this stuff the same way every time). Let's say I want to have ten different ciphers. The substitution table would be 11 columns wide by 26 rows high.

    Column 1 would contain the letters A through Z. Column 2 would contain the letters to be substituted for A-Z in Cipher #1. For example, it could be Q,W,E,R,T,Y,... ('A' in the original text would be 'Q' in the encrypted text, 'B' would be 'W', etc.). Columns 3 through 11 would have other series of letters to be substituted for A through Z. (Maybe Z,Y,X,W,V...,C,B,A or, of course, any other sequence, as long as all 26 letters are included).

    When it's time to encrypt the string in cell A1, a cipher (#1 through #10) would be chosen either randomly or by the operator.

    Then the function (to be developed) would step through the string in A1, substituting for each letter the corresponding letter in the chosen cipher. Spaces and punctuation would remain as they are in the original string.

    The output (put it in cell B1) would be the original string, with each letter suitably encrypted and with spaces and punctuation left alone. That is pretty much exactly what I want.

    It would be useful to have a second function that would work the same as the first, except a space in the original string would be converted to TWO spaces in the encrypted version. (I need the latter because to make it easier to "solve" the cryptogram, which I present in Word, I add character spacing of 5pts and line spacing of 3 lines. Without the double space, the finished product doesn't look right).

    So here's what I'd have:

    Cell A1 would contain the original string, in a monospaced font, to make things line up better.

    Cell A2 would contain that string, in the same monospaced font, with the letters substituted from whatever cipher is chosen to use for encryption. Spaces and punctuation would be in the same positions in Cells A1 and A2.

    Cell A3 would contain the same string as A2, except with double spaces instead of single ones. It won't line up with anything above it, but that's OK, since A1 and A2 are perfectly aligned.

    I could compare cells A1 and A2 visually, to confirm that the word lengths, spaces, and punctuation are still OK.

    I could copy cell A2 to my clipboard, then paste it unformatted into a Word document (maybe embedded in the Excel worksheet!), where I could add the expanded character and line spacing.

    Surely this can be done. How, I'm not sure, though I can see the beginnings of it in the stuff that Loungers have already provided.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could use something like this:
    Code:
    Option Explicit
    Function GetEncrypted(strWord As String, rngCiphers As Range, lngIndex As Long) As String
    'This function takes a string and encrypts it using a specified cipher from a range
    ' only letters are encrypted - all other symbols remain unchanged
        Dim x                   As Long
        Dim strChar             As String
        Dim varCipher           As Variant
        Dim varChars            As Variant
        
        GetEncrypted = vbNullString
        
        ' check cipher index is valid
        If lngIndex + 1 > rngCiphers.Count Then
            GetEncrypted = "#REF!"
            Exit Function
        Else
            varCipher = rngCiphers.Columns(lngIndex + 1).Value2
            varChars = rngCiphers.Columns(1).Value2
        End If
        
         'Convert to upper case
        strWord = UCase$(strWord)
        
        For x = 1 To Len(strWord) 'Act on each letter
            strChar = Mid$(strWord, x, 1)
            Select Case Asc(strChar)
            Case 65 To 90
                'strChar is a letter, so encrypt it
                GetEncrypted = GetEncrypted & Encrypt(strChar, varChars, varCipher)
            Case Else
                ' other cahrs are simply returned
                GetEncrypted = GetEncrypted & strChar
          End Select
        Next x
    End Function
    
    
    Function Encrypt(strChar As String, varChars, varCipher) As String
        Dim varMatch
    
    
        varMatch = Application.Match(strChar, varChars, 0)
        If Not IsError(varMatch) Then
            Encrypt = UCase$(varCipher(varMatch, 1))
        End If
    End Function
    Sample attached.
    Attached Files Attached Files
    Last edited by rory; 2011-07-29 at 10:11.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following User Says Thank You to rory For This Useful Post:

    Lou Sander (2011-07-29)

  7. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Many thanks. What a perfect solution!

    This is fantastic! Back in the days of Commodore, I wrote magazine articles with programs like this, and lots of people learned from and were helped by them.

    Thirty years later, the magazines are gone, and people are helping me with different flavors of the same kind of stuff. Kinda makes me want to go out and take a VBA course.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    My latest worksheet is attached, incorporating features provided by helpful Loungers.

    There was a request here, but I've removed it. I figured something out on my own.
    Attached Files Attached Files
    Last edited by Lou Sander; 2011-07-31 at 15:09. Reason: Never mind! I figured something out on my own.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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