Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    replace a trailing hyphen

    I've got a table where several cells have a hyphen (-) as the last character of the cell. Most of the other cells have a hyphen in the middle of the cell's content somewhere.

    How can I replace only the trailing hyphens of all cells in the table with other text?

    Mark

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: replace a trailing hyphen

    Try:

    <pre>Sub NoLastHyphen()
    Dim CellText As String
    Dim Cell As Range
    For Each Cell In Selection
    If Cell.HasFormula = False Then
    CellText = Trim(Cell.Value)
    If Right(CellText, 1) = "-" Then
    Cell.Value = Left(CellText, Len(CellText) - 1)
    End If
    End If
    Next Cell
    End Sub
    </pre>


    Select the range you want to clean up, then run the macro. Somebody may be able to suggest better error trapping, and it could run a little faster by not assigning the "cell.value" to a variable, and just dealing with it as "trim(cell.value)" throughout, I suppose....

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replace a trailing hyphen

    Thanks Dean. It worked great. BTW, is it only certain kinds of macros that 'Undo' won't work with, or all of them?

    Mark

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: replace a trailing hyphen

    I'm glad to hear that the macro did what you wanted!

    The 'undo' option is not available after a macro has run. The only way around this is to save your sheet immediately prior to running the macro (this can be part of the macro, if you like) and then reverting back to the saved version if you don't like the effect of the macro.

    I don't know why this is so, or whether it is a deliberate decision by the Excel group at MS, or the inadvertent effect of an otherwise unrelated decision.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: replace a trailing hyphen

    Sorry - in my last post I misread what you wanted, and thought you just wanted to delete the trailing hyphens.

    If you want to replace them with something else the best approach will depend on:

    1) Are you going to have to do this many times, or is it a one-off?
    2) are you going to replace all the trailing hyphens with the same text, or will there be variations in the text that is appended to the cells?

    If this is a one-off, or if you have many different text strings to substitute in, I would would find a couple of unused columns, and put the text you want into one column(on a row-by-row basis). In the next column I would copy in the formula (references assume that target cell is in column A, and the cell you want to substitute in is in column B, and we are entering the formula in row 2):
    <pre>=if(right(A2,1) = "-",left(A2,len(A2)-1) & B2,A2)
    </pre>

    by copying this down all the rows in the table you will have a column that contains the substituted text you want concatenated with the original text (for those cells that had a trailing hyphen) or the original text. Copy this column, and paste/special/value to place these results back in Column A.

    If this is going to happen on a recurring basis, and if the text to be substituted is unchanging (or at least, doesn't change much) you can use the same test for a trailing hyphen that was in the VBA in my last post, but substitute
    <pre>' <font color=red>NOTE</font color=red> formula corrected from original posting!
    Cell.Value = left(CellText, len(CellText) - 1) & "the string you want" </pre>

    to concatenate the text you are looking for into the cells with a trailing hyphen

    *** Geoff W Long line in "Pre" tag removed- it throws out formatting in the forum ***

Posting Permissions

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