Results 1 to 6 of 6

Delete duplicate text in cell

This is a discussion on Delete duplicate text in cell within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet that has duplicate sentences in all the cells in Column B, and I would like an ...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    88

    Default Delete duplicate text in cell

    I have a spreadsheet that has duplicate sentences in all the cells in Column B, and I would like an automated way to delete the duplicate text. For example, cell B1 contains the following text: *See spot run. *See spot run.

    I'm thinking a macro could be written to find the first asterisk then the second and then delete everything after the second asterisk. This process would be repeated on every cell in the column.

    There may be a better way, but this is all I could come up with.

    Everyone's help would be greatly appreciated.

    Thank you.

  2. #2
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,754

    Default Re: Delete duplicate text in cell

    Try this:
    Code:
    Option Explicit
    
    Sub BetweenStars()
    Dim RNG As Range
    Dim Cell As Range
    
    Set RNG = Range("B:B").SpecialCells(xlConstants)
    
        For Each Cell In RNG
            If InStrRev(Cell, "*") > 1 Then _
                Cell = Left(Cell, InStrRev(Cell, "*"))
        Next Cell
    
    End Sub
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    88

    Default Re: Delete duplicate text in cell

    Thanks, Jerry. It works great.

    What would you add to the code to make it delete the asterisks after deleting the duplicate text?

  4. #4
    Board Regular
    Join Date
    Nov 2010
    Posts
    132

    Default Re: Delete duplicate text in cell

    Hi JimH,

    I'm no good with macros but the following formula might help as long as all duplicates follow the same format (will remove all asterisks, then just paste values if required)

    =MID(B1,2,SEARCH("~*",B1,2)-3)

    Cheers,
    alx7000

  5. #5
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,754

    Default Re: Delete duplicate text in cell

    Quote Originally Posted by JimH View Post
    What would you add to the code to make it delete the asterisks after deleting the duplicate text?
    Maybe this:
    Code:
    Option Explicit
    
    Sub BetweenStars()
    Dim RNG As Range
    Dim Cell As Range
    
    Set RNG = Range("B:B").SpecialCells(xlConstants)
    
        For Each Cell In RNG
            If InStrRev(Cell, "*") > 1 Then _
                Cell = Trim(Replace(Left(Cell, InStrRev(Cell, "*")), "*", ""))
        Next Cell
    
    End Sub
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,387

    Default Re: Delete duplicate text in cell

    Hi

    If the second asterisc is always preceeded by a space, like in the example, you can also use Edit->Replace to delete the extra text. This would work in any spreadsheet that you receive without having to use macros or formulas.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com