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

    Delete certain text

    I posted this on Mr Excel and Excel Help Forum, but haven't been able to resolve it, and am under a bit of a time constraint. I have a column of data in the form "Test - Begin", "First - Second & Third", "Explanation - Discussion and Questions", etc. The common theme is that there is a space/dash/space between the first and second words in each cell. What I would like to do is delete everything after the first word in each cell. Keep in mind the first word could vary in length. Any ideas as to VBA code that would do the trick?
    Thanks in advance.

  2. #2
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    Here's a different approach: Doing one column at a time, you will split the column between the first and second words, eliminating the 2nd column and keeping the 1st column.

    Highlight one column of data. Choose Data / Text to Columns. Choose Delimited and hit Next. Check Other; uncheck all other boxes. Put the space dash space in as the delimiter next to Other. Click Next. Highlight the first column and select a format type. Highlight the 2nd column and choose Do Not Import Column. Hit Finish.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Mr Jim, Unfortunately it doesn't work: the Data/Text to Columns function will not accept a space/-/space, only a -.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Jim,
    Here is a sample of before and what I would like it to be after running the macro, etc. Column A is before and column B is after. Hope this helps.
    Jeff
    Attached Files Attached Files

  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
    J.L.

    This code should do the trick.
    Code:
    Option Explicit
    
    Sub StripJunk()
    
       Dim zItems As Variant
       Dim lRow   As Long
       
       lRow = 1
       
       [A1].Select
       
       Do While Cells(lRow, 1) <> ""
       
          If Left(Cells(lRow, 1), 12) = "Location ID:" Then
            zItems = Split(Cells(lRow, 1), " ")
            Cells(lRow, 1).Value = zItems(2)
          End If
          
          lRow = lRow + 1
          
       Loop
       
    End Sub
    There is a caveat though. You need to run a Search & Replace on Column A and Replace ID: {there are 2 spaces there} with ID: {only one space here} for the code to work properly as written. Alternatively you could change the line Cells(lRow, 1).Value = zItems(2) to Cells(lRow, 1).Value = zItems(3). However, you are at the mercy of inconsistent number of spaces after the ID: so running the S&R until you get no changes will solve this dependency.

    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hey Retired Geek,
    We meet again. You almost have it (I realize I wasn't clear enough in my previous postings). I attach a file with the desired before and after effects of the macro. In a nutshell, I need the macro to delete all text in the rows that are not affected by the macro (in this case rows 1, 3-9, 11-15, etc., so that the final outcome looks like the "After" worksheet).
    Any ideas?
    Thanks again.
    Attached Files Attached Files

  7. #7
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    Quote Originally Posted by jlkirk View Post
    Thanks Mr Jim, Unfortunately it doesn't work: the Data/Text to Columns function will not accept a space/-/space, only a -.
    Here's an easy way to get around this limitation: Do a find-and-replace. Replace " - " with "@" (ignore the quotes), or with some other character which doesn't appear anywhere in the spreadsheet. Do Replace All, to convert the entire spreadsheet at one time.

    You can then split each column into two, using the special character you used above as your delimiter. Then "Do Not Import" the 2nd column.

  8. #8
    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
    J.L.,

    Here it is:
    Code:
    Option Explicit
    
    Sub StripJunk()
    
       Dim zItems As Variant
       Dim lRow   As Long
       
       lRow = 1
       
       [A1].Select
       
       Do While Cells(lRow, 1) <> ""
       
          If Left(Cells(lRow, 1), 12) = "Location ID:" Then
            zItems = Split(Cells(lRow, 1), " ")
            Cells(lRow, 1).Value = zItems(2)
          Else
            Rows(lRow).EntireRow.ClearContents
          End If
          
          lRow = lRow + 1
          
       Loop
       
    End Sub
    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    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
    Just as a variation
    Code:
        With Range("A:A")
            .Replace "Location ID:  ", Chr(5)
            .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, other:=True, otherchar:=Chr(5)
            .EntireColumn.Delete
        End With
        Range("A:A").Replace " - *", "", xlPart
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Retired Geek and Rory. Rory, being a VBA novice, is this a complete new macro, or where do I put it?
    Thanks again guys!

  11. #11
    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
    Yes, it's a complete new macro (just needs to go inside Sub...End Sub lines)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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