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)


23843

[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. :thumb

[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