Limiting character length of excel cells/columns

[hapbryant]

Hello. I am trying to limit the number of characters in an Excel 2010 worksheet created with data imported from another program. Specifically, I need each column to have no more than 30 characters. I have tried the Data Validation technique, but this does not seem to work with imported data, either before or after it's pasted into the worksheet. I have also tried copying a macro (a first for me), as below, but this did not have the desired effect, either:



Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Excel.Range
If Not Intersect(Target, Columns("A")) Is Nothing Then For Each cell
In Intersect(Target, Columns("A"))
If Len(cell.Value) > 30 Then cell.Value = Left(cell.Value, 30) cell.
Select MsgBox "Truncated to 30 characters"
End If
Next cell
End If
End Sub


If anyone could provide assistance, I would be most grateful.

 

[GRTorres]

Hello hapbryant, I think this is what you are looking for. You had the right functions, but needed to loop a little differently. Let me know if this is not what you are looking for.

Sub LimitLength()

Set a = Sheets("Sheet1")

Dim i, j As Long
Dim leng As Long
Dim keep As String
Dim over As String
i = 1
j = 0

For i = 1 To a.Range("A" & Rows.Count).End(xlUp).Row

    For j = 0 To 100

    leng = Len(a.Range("A" & i).Offset(0, j))

        If leng <> 0 Then

            keep = Left(a.Range("A" & i).Offset(0, j), 30)
            over = Right(a.Range("A" & i).Offset(0, j), leng - 30)
            a.Range("A" & i).Offset(0, j) = keep
            a.Range("A" & i).Offset(0, j + 1) = over

        End If

    Next j

Next i
            
End Sub

Also, make sure to change Sheet1 to the name of your tab.

 

[hapbryant]

GRTorres,

Thanks so much. Your suggestion seems logical and on the right track. I'm encountering one problem, though. When I run the macro, the line

over = Right(a.Range("A" & i).Offset(0, j), leng - 30)

generates a run time error '5' Invalid procedure or call to argument.

Do you have any suggestions for how to fix that piece?

Thanks again.

 

[Snakehips]

hapbryant,

Welcome to the board.

As you say, the code above will not work. *Edit - your original, not GRTorres's
The code suggests that you are only looking to truncate data in column A and that you want a message box notification each time the code truncates data.
I don't know whether you need the code to be in the form of the above or not.
I do not know the nature nor the extent of the data you are importing so initially offer this.....

Sub Truncate()
Application.ScreenUpdating = False
For Each cell In Range("A1").CurrentRegion  'Edit to desired range
cell.Value = Left(cell, 30)
Next cell
Application.ScreenUpdating = False
End Sub

It needs to be run on the sheet after your data has been imported.
Currently it is assuming data in a contiguous range starting with A1.
If that does not suit your data then the code can be edited to suit.
It will step through all cells and reduce any data in excess of 30 characters to 30


Right click the tab of the sheet where you have the data and click 'View Code'
Paste the above code into the code pane.
Then run the code by whatever means suits.

Hope that helps.

 

[GRTorres]

change line

If leng <> 0 Then

to

If leng > 30 Then

all fixed

 

[hapbryant]

Great! Thanks to both of you. Seems to be working now! I'm really pleased I found this resource.