Formula To Replace Suffixes

[barim]

It seems that I don't have luck finding such formula that could perform the following:

Replace suffixes and it is 3 characters from right. For example, "Walking" should be changed to "Walkers", so "ing" suffix should be replaced with "ers" suffix. I have a substitute formula that replaces "ing", but when "ing" happened to be in the middle of the word it replaces those as well, which I don't want. I also have many different instances that should be replaced. Number of characters in words is not fixed it could be 5, 6 or 10.

I know this is not that difficult question. Thanks in advance.

[Paul_Hossler]

This answers the specific question for a formula that makes "ing" --> "ers" but for a general-purpose solution, you'll probably need a macro and a 'translation' table.

Easy enough, so let us know

=IF(RIGHT(H1,3)="ing", LEFT(H1,LEN(H1)-3)&"ers", H1)

Capture.JPG

[barim]

I think I will need here a macro. I tried to bring in some more instances using IF OR statement but that should be really long formula and could slow the process. Thank you Paul for looking into this.

[Paul_Hossler]

may not be 100% bullet proof but try this

Option Explicit
Sub Suffix()
    Dim wsSuffix As Worksheet
    Dim iLen As Long, iSuffix As Long
    Dim r As Range, c As Range, r1 As Range
    Dim sFrom As String, sTo As String, sOrig As String, sNew As String
    
    'current in col A, replacement in colB
    Set wsSuffix = Worksheets("Suffix")
    
    'no cells selected
    If Not TypeOf Selection Is Range Then Exit Sub
    'make sure selection has text cells and just use them
    Set r = Nothing
    On Error Resume Next
    Set r = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
    On Error GoTo 0
    
    'no text cells
    If r Is Nothing Then Exit Sub
    
    'just single cell
    If r.Cells.Count = 1 Then
        Set r1 = r
    Else
        Set r1 = Selection
    End If
    
    'loop suffixes then loop selection cells
    With wsSuffix
        For iSuffix = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
            sFrom = Trim(.Cells(iSuffix, 1).Value)
            sTo = Trim(.Cells(iSuffix, 2).Value)
            iLen = Len(sFrom)
    
            For Each c In r1.Cells
                sOrig = Trim(c.Value)
                If Len(sOrig) = 0 Then GoTo NextCell
                If Len(sOrig) < iLen Then GoTo NextCell
                If Right(sOrig, iLen) <> sFrom Then GoTo NextCell
                    
                sNew = Left(sOrig, Len(sOrig) - iLen) & sTo
    
                c.Value = sNew
    
NextCell:
            Next
        Next
    End With
End Sub