Delete data from cells that exceed 35 characters

[lofty_d]

Hi, I have a spreadsheet that already has data in it. I want a macro to remove any data from the spreadsheet where the cell exceeds 35 characters in length.

I thought Data -> Validation might help but as the data is already in the spreadsheet it does not.

Any help would be appreciated.

 

[patel45]

Sub a()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
  If Len(cell.Text) > 35 Then cell.Value = ""
Next
End Sub

 

[Marcol]

Try this ....
In a spare column

=IF(LEN(A2)>35,"Yes","")

Drag/Fill Down

Filter the data on this helper column for "Yes"
By selecting the resulting rows, you can now either clear or delete it as required.

Then remove the filter to see the new table.

Excel Workbook
	A	B
1	Data	Clear or Delete
2	xxxxxxxxxx
3	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx	Yes
4	xx
5	xxxxxxxxx
6	xxxxxxxxxxxxxxxxxxxxxxxxxx
7	xxxxxxxxxx
8	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx	Yes
9	xxxxxxxx
10	xxxxxxxxxxxxxxxxx
11	xxxxxxxxxxxx
Sheet1

 

[rollis13]

But must the cells that exceed 35 characters be cleared or trimmed down to 35 ?

 

[lofty_d]

Thank you both for the responses, both very useful, I should have explained more clearly, for any cells with more than 35 characters, I want the cell trimmed down to the 35 characters...

E.g.

aaaaaaaaaabbbbbbbbbbccccccccccdddddddddd

becomes

aaaaaaaaaabbbbbbbbbbccccccccccddddd

Is this possible?

 

[taurean]

EDIT: This is based on your last post...!
See if this Marcol's formula wrapped in VBA work for you.

Public Sub EraseOver35()
With Sheets("Sheet1").UsedRange
    .Value = Evaluate("IF(LEN(" & .Address(0, 0) & ")>35,LEFT(" & .Address(0, 0) & ",35)," & .Address(0, 0) & ")")
End With
End Sub

 

[lofty_d]

EDIT: This is based on your last post...!
See if this Marcol's formula wrapped in VBA work for you.

Public Sub EraseOver35()
With Sheets("Sheet1").UsedRange
    .Value = Evaluate("IF(LEN(" & .Address(0, 0) & ")>35,LEFT(" & .Address(0, 0) & ",35)," & .Address(0, 0) & ")")
End With
End Sub

This has worked perfectly, thank you!!!!!!!

 

[Marcol]

Without VBa ...

Drag B2 Down

Excel Workbook
	A	B
1	Data	Trim then Copy > Paste Special > Values
2	xxxxxxxxxx	xxxxxxxxxx
3	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
4	xx	xx
5	xxxxxxxxx	xxxxxxxxx
6	xxxxxxxxxxxxxxxxxxxxxxxxxx	xxxxxxxxxxxxxxxxxxxxxxxxxx
7	xxxxxxxxxx	xxxxxxxxxx
8	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
9	xxxxxxxx	xxxxxxxx
10	aaaaaaaaaabbbbbbbbbbccccccccccdddddddddd	aaaaaaaaaabbbbbbbbbbccccccccccddddd
11	xxxxxxxxxxxx	xxxxxxxxxxxx
Sheet1

 

[lofty_d]

Hi, would you be able to advise on a couple of amendments, I have been using:

Public Sub EraseOver35()

With Sheets("Sheet1").UsedRange
.Value = Evaluate("IF(LEN(" & .Address(0, 0) & ")>35,LEFT(" & .Address(0, 0) & ",35)," & .Address(0, 0) & ")")
End With

End Sub

It has been working fine, however:

1. After it trims the character lengths, any blank cells are filled in with just zeros. Is there anyway to adjust this so the cells are just left blank?

2. Rather than this code affecting the whole spreadsheet, how do I adjust this so it only looks at certain columns? I.e. only columns B-K are adjusted.

Thanks in advance.

</PRE>

 

[taurean]

You get general approach with general query.

Can you explain the exact (row) range that should be worked out by this macro? e.g. from row 5 to last row as per column B or whatever that explains your situation.