replace a trailing hyphen

Topic

New Reply

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

Reply | Quote

Replies

Try:

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

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….

Reply | Quote

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):

=if(right(A2,1) = "-",left(A2,len(A2)-1) & B2,A2)

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

' NOTE formula corrected from original posting!
Cell.Value = left(CellText, len(CellText) - 1) & "the string you want"  

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 ***

Reply | Quote

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

Mark

Reply | Quote

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.

Reply | Quote