remove last character from a column

[AS400Excel]

I want to delete the last character from "each cell in a column". Can some one provide the code for this kind of a macro?.

Thanks

 

[Oaktree]

Welcome to the board!

=LEFT(A1,LEN(A1)-1) will do this with a formula, then you can copy it down.

 

[AS400Excel]

Thanks for the reply.
But this will take care of only one cell.
I want to delete last character in each cell under a column.

 

[Zack Barresse]

How many sheets? All? How many workbooks?

 

[Todd Bardoni]

That's why he said copy the formula down...meaning copy it all the way down your column.

 

[Oaktree]

Right, but you should be able to copy the formula down (move your cursor to the bottom right of the cell and double click once the cursor changes to a small, black +)

 

[Zack Barresse]

Btw, do these happen to be spaces? And how many columns?

 

[Todd Bardoni]

Sub test()
    On Error Resume Next
    For Each cel In Range("A1:A10")
        myVal = cel.Value
        cel.Value = Left(myVal, Len(myVal) - 1)
    Next cel
End Sub

 

[AS400Excel]

I want to copy a column from an other worksheet and then copy it to the template where the last char in each cell under that column gets removed.

I created the formula/func in row A1 and copied the same across till the last row. But when I copy the column from another worksheet the formula is getting replaced with the actual values.

I can use the above given Macro, thanks.
But can I do it without Macro.

Thanks

 

[Zack Barresse]

Use Oaktree's solution, copy down as needed. Select it all, copy it. Press Alt + E, S, V. This will paste special / values over the formulas.