truncating

inho78 · #1 (**permalink**) 11-18-04, 12:37

this is great advice guys Ill try to run it both ways.
also,

2.) how do i create a macro(procedure) to turncate everything in the cells of column if it is greater than 255 characters?

thanks guys

shades · #2 (**permalink**) 11-18-04, 12:42

You can use a function in Excel (if necessary, record a macro as you type in the formula).

=LEFT(A1,255)

inho78 · #3 (**permalink**) 11-18-04, 12:50

thing is , im really not familiar with excel...i know how to record the macro but where do i stick that formula. sorry man

shades · #4 (**permalink**) 11-18-04, 13:55

Quote:

Originally Posted by inho78

thing is , im really not familiar with excel...i know how to record the macro but where do i stick that formula. sorry man

When you turn on the recorder:

Select the first cell next to where you want the change, i.e., if your data is in A1:A10, then click in B1, and type the formula. Then hit return. If you want to fill that down column B, then do so.

Turn off recorder.

Now go into VBE (from Excel hit ALT+F11). On the left side you will see the Explorer Window. You should see the workbook you are using, in the folder below you should see Modules, with Module1 (the one you just recorded). Double-click on it, and that module will appear on the right side. Look at that
and you will see how the formula is entered.

HTH

inho78 · #5 (**permalink**) 11-19-04, 11:04

thanks shades,
i just ended up inserting another column and putting in the formula in one of the cell and scrolling all the way down to last cell. so if the column i wanted to trunc was in column J than I put the forumla into K ran the formula all the way down the last cell and just deleted J and saved the macro.

However, how do i save the macro by making it do the formula all the way to the end of the last record(row) not the end of the column. I need have excel find the last row since the spreadsheet updated everyday.
thanks man

DavidCoutts · #6 (**permalink**) 11-19-04, 11:15

OK

A quick suggestion for you lets say the column your working on is C then we can use

looping through code

Code:

Sub Test()
    Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, 3).End(xlUp).Row
    For i = 1 To LastRow
        Cells(3, i).Formula = Left(Cells(3, i), 255)
    Next i
End Sub

or using formula

Code:

Sub Test2()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 3).End(xlUp).Row
    Cells(1, 3).EntireColumn.Insert
    Range(Cells(1, 3), Cells(LastRow, 3)).FormulaR1C1 = "=LEFT(RC[1],255)"
    
End Sub

HTH
Dave

inho78 · #7 (**permalink**) 11-19-04, 14:33

dave,
will this code pretty much do the whole process? If i wanted to do this for column "L" how do i modify this code do i replace "3" with "12"?

DavidCoutts · #8 (**permalink**) 11-22-04, 03:21

Hi inho78,

Code listing 1 replaces the formula in column C with the truncation to 255 characters from line 1 to the last row of data in that column (in the loop swap the number and the variable over i always get things mixed up when using the cells command)

Code lisiting 2 adds in a column then uses the left function in the cells from row 1 to the lastrow of data but leaves it there what you might want to do is replace the formulas with values then delete out the old column,

and yes the cells comand works like Cells(RowNumber, ColumnNumber) so A=1 B=2 etc.

Dave

inho78 · #9 (**permalink**) 11-24-04, 12:45

Thanks alot david
but when I ran this code

Sub Test()
Dim LastRow As Long
LastRow = Cells(Rows.Count, 12).End(xlUp).Row
Cells(1, 12).EntireColumn.Insert
Range(Cells(1, 12), Cells(LastRow, 12)).FormulaR1C1 = "=LEFT(RC[1],255)"

It worked but it created the modified column next to the original one and when I delete the original one, it displays a bunch of these in the modified column #REF!
how do i change this. the macro, should only chage the original column

DavidCoutts · #10 (**permalink**) 11-25-04, 03:12

Right all i did was add a formula to your worksheet and left it at that,
WHat you want is to modify your code such that it replaces the formulas with values i.e. copy -> pastespecial Values before deleteing out the old column

ive modified your code below

Code:

Sub Test()
Dim LastRow As Long
    LastRow = Cells(Rows.Count, 12).End(xlUp).Row
    Cells(1, 12).EntireColumn.Insert
    With Range(Cells(1, 12), Cells(LastRow, 12))
        .FormulaR1C1 = "=LEFT(RC[1],255)"
        .Formula = .Value
    End With
    Cells(1, 13).EntireColumn.Delete
End Sub

you may want to rename the code to something more appropriate and add comments to let you know what the code is actually doing

Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech