Results 1 to 10 of 10

Thread: truncating

  1. #1
    Join Date
    Sep 2004
    Posts
    113

    Unanswered: truncating

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    You can use a function in Excel (if necessary, record a macro as you type in the formula).

    =LEFT(A1,255)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Sep 2004
    Posts
    113
    thing is , im really not familiar with excel...i know how to record the macro but where do i stick that formula. sorry man

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    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
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Sep 2004
    Posts
    113
    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

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  7. #7
    Join Date
    Sep 2004
    Posts
    113
    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"?

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  9. #9
    Join Date
    Sep 2004
    Posts
    113
    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

  10. #10
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •