locked
Delete Right Word: Excel 2013 VBA RRS feed

  • Question

  • Hi all:

    I have a value in a cell:

    =============

    Acme Anvil Corporation (ADREA77A)

    =============

    I would like to delete the RightWord and the parentheses.  The far right word will always be surrounded by parens.

    What is the VBA for Excel 2013?

    Thanks!


    A. Wolf


    A. Wolf

    Tuesday, February 10, 2015 12:09 AM

Answers

  • Like this for the currently active cell:

    Sub DeleteWord()
        Dim p As Long
        With ActiveCell
            p = InStr(.Value, "(")
            If p > 0 Then
                .Value = Trim(Left(.Value, p - 1))
            End If
        End With
    End Sub

    If you want to apply it to a specific cell, replace ActiveCell with that cell, e.g.

        ...
        With Range("D46")
            ...

    for cell D46.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by amywolfie Tuesday, February 10, 2015 5:25 PM
    Tuesday, February 10, 2015 6:47 AM

All replies

  • Like this for the currently active cell:

    Sub DeleteWord()
        Dim p As Long
        With ActiveCell
            p = InStr(.Value, "(")
            If p > 0 Then
                .Value = Trim(Left(.Value, p - 1))
            End If
        End With
    End Sub

    If you want to apply it to a specific cell, replace ActiveCell with that cell, e.g.

        ...
        With Range("D46")
            ...

    for cell D46.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by amywolfie Tuesday, February 10, 2015 5:25 PM
    Tuesday, February 10, 2015 6:47 AM
  • Another great solution, Hans!

    Thanks again.


    A. Wolf

    Tuesday, February 10, 2015 5:25 PM