Chomp / Remove Characters after certain # of characters reached?

[essential_clix]

Hi,

Is there a way to "chomp" or remove excess characters from a cell after a certain number of characters have been reached?

For instance, say Column D has 500+ cells, all of which contain alphanumeric data (about 70 characters including spaces). I need to mass-remove (VBA?) the characters that come after the first 55 characters. The first 55 characters (including spaces) would stay in place.

Make sense? If not, please let me know and I'll try my best to reword the question.

Thanks a bunch guys n gals,
EC

[rbrhodes]

Hi EC,

Supposing your data is in Col A then a formula version could be placed in Col ?

=If(LEN(A1)>55, LEFT(A1,55), A1) 

This will echo a 55 char string from Col A. To trim it to values you would use Copy/Patespecial/Values/

A VBA version would be:

Sub fiftyfive() 
     
     ' Get rownumber of lastrow of data Col A
    lastrow =  Range("a65536").End(xlUp).Row 
     
     ' Check row 1 to last row #, Col A
    For i = 1 To lastrow 
         ' If longer than 55 chars
        If Len(Cells(i, 1)) > 55 Then 
             ' Cut to 55 chars
            Cells(i, 1) = Left(Cells(i, 1), 55) 
        End If 
         ' Not longer than 55 - get next row
    Next i 
     
End Sub 

This version would replace the original values. Change the line:

 ' Cut to 55 chars
Cells(i, 1) = Left(Cells(i, 1), 55) 
 
To 
 
Cells(i, 2) = Left(Cells(i, 1), 55) 
 

and it adds the new values to Col B...


Cheers,

dr

[essential_clix]

Hey thanks a million, man! The first code worked like a charm!

Thanks again and have a good one!

[pangolin]

Incidentally one easy way to "dynamically" control the character length of a cell is to use DATA>> VALIDATION>>SETTINGS and there select TEXT LENGTH (which works also for numerics)

[Krishnakumar]

Hi

=LEFT(A1, LOOKUP(9.999E+307, FIND(" ",A1&" ",ROW($A$1:$A$55))))

[essential_clix]

Originally Posted by pangolin

Incidentally one easy way to "dynamically" control the character length of a cell is to use DATA>> VALIDATION>>SETTINGS and there select TEXT LENGTH (which works also for numerics)

Man, I love learning new tricks like that! Thanks!