remove last character from a column

AS400Excel

New Member
Joined
Jan 27, 2005
Messages
12
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Welcome to the board!

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

AS400Excel

New Member
Joined
Jan 27, 2005
Messages
12
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
How many sheets? All? How many workbooks?
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042

ADVERTISEMENT

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

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
Code:
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

New Member
Joined
Jan 27, 2005
Messages
12
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Forum statistics

Threads
1,147,507
Messages
5,741,564
Members
423,667
Latest member
Kai_357

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top