40 characters

  • Thread starter Thread starter forum
  • Start date Start date
F

forum

Hi there i am trying to cut a piece of work down so that each excell row
only has 40 characters in it is there any way to achieve this without
going through the 5000 or so rows and doing it manually anything would
be a great help ;) :cool: :eek: :confused: :mad:
 
It depends on how your cells are listed. Is it all in a row, like
B1:B5000? Or is it all over, like A1:Z1000

The reason I ask is because to set the macro going I need to know what
kind of range to specify, and when to tell the macro to stop. Other
than that, it's just doing

TheCell = Left(TheCell, 40)

Maybe somebody else knows a different way?

-Bob
 
What does that mean? Give us some examples. The easier you make it for
people to help you, the faster you'll get your answer.
 
Well, this works, for what I did. Not sure if it's exactly what you
wanted though.

I made a cell, B2, which contained =COUNTA(A1:IV1) (Count the number of
non blank cells between A and IV (the last column))

That will only work if there are no blank cells between the start and
the end of your data. If there is, I'd use a Do->While ActiveCell<>B2
(not sure if that's the actual code) or similar.

Anyway, you've got the counter in B2, and all the long data (I copy
pasted 80 charachters into each cell) And then made a new VBA Module
that looked like this.

'general declarations
Dim cellscount
Dim cellcontents
Dim cellarea
Sub Trimmer()
Application.ScreenUpdating = False
cellscount = [A2]
cellscount = cellscount - 1
Cells(1, 1).Activate
cellcontents = ActiveCell.Value
ActiveCell.Value = Left(cellcontents, 40)
For cellarea = 0 To cellscount
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
cellcontents = ActiveCell.Value
ActiveCell.Value = Left(cellcontents, 40)
Next cellarea
Cells(1, 1).Activate
Beep
Application.ScreenUpdating = True
End Sub

If anybody has a better way of doing this, feel free. I'm as much
interested in more versatile code as the next person.

Hope it helps.

-Bob
 
Select your range first.
Then run a macro that uses Bob's suggestion:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub
Else
For Each myCell In myRng.Cells
myCell.Value = Left(myCell.Value, 40)
Next myCell
End If
End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Another option (if you mean the cells are in a single column) is to select the
range of cells, then data|text to columns.

choose fixed width and chop it after column 40.
Skip the columns to the right of the first 40 characters.

(note that this will trim leading spaces from the cell, too--it might not be
what you want if you want to keep those.)
 
Back
Top