If the # of characters exceed 40 in a cell then parts of the value in this cell gets removed

[zack8576]

I need to modify some excel files, some of the values in certain cells in column K exceeds 40
when this happens, I will need to remove these characters from the cell: (SAN), (WET), COVER (any of these cell will only contain one of these words, SAN and WET always has the parenthesis)
example column K values below:

(WET)MH,8'dia,Base,10"w,SOG,144x14"BS,waterstop (SAN)MH,4'dia,Base,8"w,XYPEX,Drop,Invert,30" USF AS COVER,Orange County Sanitary, BOCC,FL Code below, this is not working so far... any help is appreciated

Sub CleanUpStringsLongerThanForty()
    With Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
        If Len("K2:K") > 40 Then
            Replace("COVER", "(SAN)", "(WET)", "")
        End If
    End With
End Sub

 

[bebo021999]

Replace function works for single cell, not range
Should be:

Option Explicit
Sub CleanUpStringsLongerThanForty()
Dim cell As Range
For Each cell In Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
    If Len(cell) > 40 Then
        cell.Replace "(SAN)", ""
        cell.Replace "(WET)", ""
        cell.Replace "COVER", ""
    End If
Next
End Sub

 

[Peter_SSs]

Another way that you could try

Sub CleanUpStringsLongerThanForty_v2()
  With Range("K2", Range("K" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(len(#)>40,substitute(substitute(substitute(#,""(SAN)"",""""),""(WET)"",""""),""COVER"",""""),#)", "#", .Address))
  End With
End Sub

 

[zack8576]

Another way that you could try

Sub CleanUpStringsLongerThanForty_v2()
  With Range("K2", Range("K" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(len(#)>40,substitute(substitute(substitute(#,""(SAN)"",""""),""(WET)"",""""),""COVER"",""""),#)", "#", .Address))
  End With
End Sub

thank you Peter !

 

[zack8576]

Replace function works for single cell, not range
Should be:

Option Explicit
Sub CleanUpStringsLongerThanForty()
Dim cell As Range
For Each cell In Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
    If Len(cell) > 40 Then
        cell.Replace "(SAN)", ""
        cell.Replace "(WET)", ""
        cell.Replace "COVER", ""
    End If
Next
End Sub

thank you Bebo ! this works fine

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.