To simply combine them you can place them all together like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_AB","_"),"_CD","_"),"_EF","_"),"_40K",""),"_60K",""),"_S_","_"),"_","-")

(note that this may pass the older Excel limit of 7 nested statements. I'm testing in Excel 2010

Another way to do it is by utilizing Left and Right functions.

This assumes that the changing data on the end is always present and is 8 characters long

=SUBSTITUTE(LEFT(A2,LEN(A2)-8),"_","-")

This will achieve the same resulting string

If the string doesn't always end with 8 characters that you want to strip off you can search for the "_S" and get the current location. Try this:

=SUBSTITUTE(LEFT(A2,FIND("_S",A2,1)),"_","-")

in cell b1: =SUBSTITUTE(A1,"--","-") you can then copy this across to c1, d1, e1 etc. Each further cell to the right will have one less - than the previous. Add columns until all hyphens are dealt with.

This way if you add a new row where there are more hyphens than in previous rows, you can just make extra columns, instead of changing the formula which gets messy.

You do not want to read and write the cell multiple times. You only do a read and a write. You also need to check if the cell has a string. There is no point in running the replace on numbers or errors. Something like:

Dim v As Variant
Dim i As Long
Dim oldLen As Long

For Each cell In rng
    v = cell.Value2
    If VarType(v) = vbString Then
        oldLen = Len(v)
        For i = 25 To 23 Step -1
            v = WorksheetFunction.Substitute(v, " ", "  ", i)
        Next i
        If Len(v) <> oldLen Then cell.Value2 = v
    End If
Next

Or, if you want to be efficient then you read the range into array and replace the whole thing in one go:

Dim rngArea As Range
Dim arr() As Variant
Dim i As Long
Dim j As Long
Dim lastRow As Long
Dim hasChanged As Boolean
Dim v As Variant
Dim oldLen As Long

For Each rngArea In rng.Areas
    'Read range into array of values
    If rngArea.Count > 1 Then
        arr = rngArea.Value2
    Else
        ReDim arr(1 To 1, 1 To 1)
        arr(1, 1) = rngArea.Value2
    End If
    
    i = 1
    j = 1
    lastRow = UBound(arr, 1)
    hasChanged = False
    For Each v In arr 'Traverse the 2D array column-wise
        If VarType(v) = vbString Then
            oldLen = Len(v)
            For i = 25 To 23 Step -1
                v = WorksheetFunction.Substitute(v, " ", "  ", i)
            Next i
            If Len(v) <> oldLen Then
                arr(i, j) = v
                hasChanged = True
            End If
        End If
        i = i + 1
        If i > lastRow Then
            i = 1
            j = j + 1
        End If
    Next v
    If hasChanged Then
        rngArea.Value2 = arr
    End If
Next rngArea