VBA code adding values to cells instead of removing

[zack8576]

I have this macro that removed certain parts of the text if the number of characters in any cell in column K is longer than 40.
However, it is also adding a 0 to any cell in K when the number of characters is 0. See picture below
code also below. Any help is greatly appreciated

Sub CleanUpStringsLongerThanForty()
   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

 

[Fluff]

How about

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

 

[zack8576]

How about

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

Fluff, thank you. This works great
so the

#="""","""",

means the text string in column K has to contain at least 2 text strings with comma between the two for this code to execute ?

 

[Fluff]

No it means if the cell is empty leave it that way.

 

[zack8576]

No it means if the cell is empty leave it that way.

thank you !

 

[Fluff]

You're welcome & thanks for the feedback.