# Thread: Formula To Replace Suffixes

1. ## Formula To Replace Suffixes

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.  Reply With Quote

2. 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  Reply With Quote

3. 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.   Reply With Quote

4. 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```  Reply With Quote

#### Posting Permissions

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