VBA to find and replace and sort the valueswithin each cell

I made the following changes:
1. Created a two column Table in worksheet Replacements with the original text and its desired replacement
2. Added a button and instructions for how to use the macro to worksheet Data
3. Corrected some apparent typos in worksheet Data column A to make the test cases consistent with the desired results
4. Revised the macro to point to the two column Table
5. Revised the macro to separate each API with " + nn " where nn is the number from column B on worksheet Replacements
6. Stored the code in a regular Module, and saved the file with .xlsm file extension

Note that you must enable macros when you open the workbook.

Sub ReplaceAndSort()
Dim s As String, s1 As String, separator As String
Dim Replacements As Variant, v As Variant
Dim i As Long, j As Long, k As Long, n As Long, nRows As Long, nWords As Long
Dim cel As Range, rg As Range
Dim b As Boolean

separator = "|"     'This should be a character that never appears in the source text

Application.ScreenUpdating = False
Replacements = Worksheets("Replacements").ListObjects(1).DataBodyRange.Value
Set rg = Selection
Set rg = Intersect(rg, rg.Worksheet.UsedRange)

nRows = rg.Rows.Count
n = UBound(Replacements)

For j = 1 To n
    s1 = Replace(Replacements(j, 2), " ", Chr(160))
    rg.Replace Replacements(j, 1), separator & " " & s1, MatchCase:=False, LookAt:=xlPart
Next

For Each cel In rg.Cells
    v = Split(" " & cel.Value, separator)
    nWords = UBound(v)
    If nWords > 0 Then
        For i = 1 To nWords
            b = False
            For k = 1 To nWords
                If v(k - 1) > v(k) Then
                    s = v(k - 1)
                    v(k - 1) = v(k)
                    v(k) = s
                    b = True
                End If
            Next
            If b = False Then Exit For
        Next
        s = Application.Trim(Join(v, " "))
        s = Application.Trim(Replace(s, "+", " "))
        s = Replace(s, " ", " + ")
        s = Replace(s, Chr(160), " ")
        cel.Value = Application.Trim(s)
     End If
Next
End Sub

Select allOpen in new window

WheezerQ29165985.xlsm

Thanks a lot for the code could you please explain each and every line of the code and send me it would be very useful for me to workaround with it.

Also this is just for one column i have three similar column.I see in the code you have used listobjestcs(1) in the code to refer the table .If i want to use three other columns how do i refer it after creating a table for it.Thanks in advance

Am very sorry for that because i don't want to share the file because its sensitive.Attached the file with three columns which has to be replaced and sorted .Ignore if there are no values for replacement.
Relacement.xlsx

Yes that + will always separate different terms.yeah as of now there are no spaces i have checked several workbooks,in caes lets say tmrw for a two or three cells has spaces . please consider tat part as well . thanks in advance.i have checked your code and it works like champ.you are superb dude.

Thanks alot buddy Sorry for an late reply.the code works like an champ.Still i have two more questions.this very small you can explain me in words if its not complicated.1.Now the replacement columns are in 1 ,2,3  lets say if its in 5,6,7 how do i change it accordingly.one i should change in master columns(5),columns(6),columns(7). but in replacement sub procedure am not sure where to change.2.i want to highlight if the value doesnt get replaced with values.So that it will be easier for me to QC. thank you

Thanks alot

Hi Need an additional help on the same.once the cells value sorted by numbers I want to remove the numbers .Could you please add an additional code for the same please.

Hi Thanks,

After doing quality checks with the automation i found one bug and it was sent from my manager if you can notice row a566:a571 its FIXED ICS\LABA after replacement it should be 05 FIXED ICS\LABA ,but its getting replaced as 04 FIXED LAMA\LABA.Can you fix it i just noticed only this  bug,there  are chances to be many .also can we go to previous methodology like creating three tables with columns"find this" and "replace ".

In the above attached file when i comment delete codes and run the macro the replacement and sorting doesn't happen for all the rows.Can i know why please.replacement and sorting  has happened only for rows 572 :579.

Hi ,
You have created a procedure Sub DeleteCodes(rg As Range) which After sorting, the two-digit numbers will be removed.is it possible to remove blank spaces after each string