VB formula to clear cell content exceeding dcharacters

[ATY807]

Hello,

I need a VB code that clears contents of cells of a selected column in an Excel sheet when the number of characters exceeds 12

Thank you,

 

[ChrisFoster]

Try this code - the range is set to column A but you can amend that as needed.

Sub ClearCells()
    Dim cel As Range
    For Each cel In Range("A:A")
        If Len(cel.Text) > 12 Then cel.ClearContents
    Next cel
End Sub

 

[Rick Rothstein]

Here is another macro that you can try...

Sub ClearIfMoreThan12Characters()
  Columns("A").Replace "?????????????*", "", xlPart, , , , False, False
End Sub

EDIT NOTE: You can replace Columns("A") with Selection since that is what you apparently asked for originally.

 

[ATY807]

Thanks Rick. How do I modify it to keep only 12 characters,

Try this code - the range is set to column A but you can amend that as needed.

Sub ClearCells()
    Dim cel As Range
    For Each cel In Range("A:A")
        If Len(cel.Text) > 12 Then cel.ClearContents
    Next cel
End Sub

Thank you for your reply.

Missed an End if statement, otherwise works. A little slow due to looping over entire column A:A selection, need to specify the exact range in which there are cell contents.

 

[ATY807]

Here is another macro that you can try...

Sub ClearIfMoreThan12Characters()
  Columns("A").Replace "?????????????*", "", xlPart, , , , False, False
End Sub

EDIT NOTE: You can replace Columns("A") with Selection since that is what you apparently asked for originally.

Thank you

 

[Fluff]

How about

Sub aty()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("if(len(" & .Address & ")<>12,""""," & .Address & ")")
   End With
End Sub

 

[Fluff]

Or if you want it to work on a selected column, use

Sub aty()
   With Intersect(ActiveSheet.UsedRange, Selection.EntireColumn)
      .Value = Evaluate("if(len(" & .Address & ")<>12,""""," & .Address & ")")
   End With
End Sub