Consulting

Results 1 to 4 of 4

Thread: Formula To Replace Suffixes

  1. #1
    VBAX Regular
    Joined
    Jan 2016
    Posts
    55
    Location

    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.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,872
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Jan 2016
    Posts
    55
    Location
    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.

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,872
    Location
    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
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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