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

    Letters to individual cells

    I have a recurring task that seems like it could be automated, but I don't know where to start.

    Every few days, I need to process a different sentence whose length is 75-125 characters.

    I put the sentence into cell A1 of a worksheet with 125 columns, each wide enough to hold one letter. There's nothing in cell A2 or any cell to the right of it, so I can see the entire sentence, spanning cells from A1 to wherever it happens to end.

    I need to map the letters into individual cells. For example, the first letter needs to go into C1, the second to C2, the third to C3, etc.

    I have been typing them in individually, which is tedious, not particularly so. More importantly, it is subject to typing errors, which is unacceptable in my application. And if I DO make an error, I have to type every letter in again.

    Any suggestions on how I can avoid this undesirable typing?
    Last edited by Lou Sander; 2011-07-25 at 10:31.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    This will do what you want:
    Code:
    Option Explicit
    
    Sub SentenceSplit()
    
       Dim zMyStr As String
       Dim iCnt   As Integer
       
       zMyStr = [A1].Value
       [C1].Select
       
       For iCnt = 1 To Len(zMyStr)
          ActiveCell.Offset(iCnt - 1, 0).Value = Mid$(zMyStr, iCnt, 1)
       Next iCnt
      
    End Sub
    You did not specify what to do with spaces in the sentence.

    Ignore Spaces version:
    Code:
    Sub SplitSentenceNoSp()
    
       Dim zMyStr As String
       Dim zCurCh As String
       Dim iCnt   As Integer
       Dim iNext  As Integer
       
       iNext = 0
       zMyStr = [A1].Value
       [C1].Select
       
       For iCnt = 1 To Len(zMyStr)
       
          zCurCh = Mid$(zMyStr, iCnt, 1)
          If zCurCh <> " " Then
            ActiveCell.Offset(iNext, 0).Value = zCurCh
            iNext = iNext + 1
          End If
          
       Next iCnt
      
    
    End Sub
    Attachment has both versions included.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2011-07-25 at 11:11. Reason: Update Attachment & code
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #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
    Thanks! I'll let you know if it works, which of course it will.

    Except for the VBA keywords and syntax, this is like BASIC on the old Commodore PET, which I used to do a LOT of back in the 80s.

    My project involves making clever cryptograms. I do some manipulation in Word, but I use Excel to count the letters and do a final check. I got into an embarrassing situation by turning out bad work on one of 'em, so I'm trying to automate.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Well, it ALMOST works. It puts the letters into different adjacent rows. I need them in different adjacent COLUMNS.

    I don't know enough Excel-specific VBA to make that happen. Hopefully, you do, and it's not too hard.

    If it helps, the letters are to be plugged into a range named 'Letters'. It's 175 columns wide, with each column wide enough to hold one letter.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    You said:
    I need to map the letters into individual cells. For example, the first letter needs to go into C1, the second to C2, the third to C3, etc.
    Than goes down Column C!

    So which Row do you want them spread across? If you can attach an example.
    Code:
    Sub SentenceSplit()
    
       Dim zMyStr As String
       Dim iCnt   As Integer
       
       zMyStr = [A1].Value
       [C1].Select
       
       For iCnt = 1 To Len(zMyStr)
       '*** Comment out the one you DON'T want ***
          ActiveCell.Offset(0, iCnt - 1).Value = Mid$(zMyStr, iCnt, 1) 'Across Row
    '      ActiveCell.Offset(iCnt - 1, 0).Value = Mid$(zMyStr, iCnt, 1) 'Down Column
       Next iCnt
      
    End Sub
    
    Sub SplitSentenceNoSp()
    
       Dim zMyStr As String
       Dim zCurCh As String
       Dim iCnt   As Integer
       Dim iNext  As Integer
       
       iNext = 0
       zMyStr = [A1].Value
       [C1].Select
       
       For iCnt = 1 To Len(zMyStr)
       
          zCurCh = Mid$(zMyStr, iCnt, 1)
          If zCurCh <> " " Then
          '*** Comment out the one you DON'T want ***
            ActiveCell.Offset(0, iNext).Value = zCurCh  'Across Row
    '        ActiveCell.Offset(0, iNext).Value = zCurCh  'Down Column
            iNext = iNext + 1
          End If
          
       Next iCnt
    Last edited by RetiredGeek; 2011-07-25 at 21:56.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Lou Sander (2011-07-25)

  7. #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
    Sheesh! I specified rows instead of columns. Sorry.
    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
  •