Delete data from cells that exceed 35 characters

lofty_d

New Member
Joined
Sep 15, 2012
Messages
7
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

patel45

Well-known Member
Joined
Jul 15, 2012
Messages
1,953
Code:
Sub a()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
  If Len(cell.Text) > 35 Then cell.Value = ""
Next
End Sub
 

Marcol

Well-known Member
Joined
Mar 1, 2010
Messages
644
Try this ....
In a spare column
Code:
=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
AB
1DataClear or Delete
2xxxxxxxxxx 
3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxYes
4xx
5xxxxxxxxx
6xxxxxxxxxxxxxxxxxxxxxxxxxx
7xxxxxxxxxx
8xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxYes
9xxxxxxxx
10xxxxxxxxxxxxxxxxx
11xxxxxxxxxxxx
Sheet1
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
487
Office Version
  1. 2016
Platform
  1. Windows
But must the cells that exceed 35 characters be cleared or trimmed down to 35 ?
 
Last edited:

lofty_d

New Member
Joined
Sep 15, 2012
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Jun 17, 2011
Messages
2,189
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
EDIT: This is based on your last post...!
See if this Marcol's formula wrapped in VBA work for you.
Code:
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
 
Last edited:

lofty_d

New Member
Joined
Sep 15, 2012
Messages
7

ADVERTISEMENT

EDIT: This is based on your last post...!
See if this Marcol's formula wrapped in VBA work for you.
Code:
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

Well-known Member
Joined
Mar 1, 2010
Messages
644
Without VBa ...

Drag B2 Down

Excel Workbook
AB
1DataTrim then Copy > Paste Special > Values
2xxxxxxxxxxxxxxxxxxxx
3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
4xxxx
5xxxxxxxxxxxxxxxxxx
6xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
7xxxxxxxxxxxxxxxxxxxx
8xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
9xxxxxxxxxxxxxxxx
10aaaaaaaaaabbbbbbbbbbccccccccccddddddddddaaaaaaaaaabbbbbbbbbbccccccccccddddd
11xxxxxxxxxxxxxxxxxxxxxxxx
Sheet1
 

lofty_d

New Member
Joined
Sep 15, 2012
Messages
7
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

Well-known Member
Joined
Jun 17, 2011
Messages
2,189
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,396
Messages
5,746,447
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top