Solved

Need to remove last 3 characters in each excel cell

Posted on 2011-09-07
4
249 Views
Last Modified: 2012-05-12
I have a column that's has about 200 cells in it and I need to remove the last 3 characters from each cell.

 i found this:

For Each cell In Selection
cell.Value = LEFT(cell,LEN(cell)-3)
Next cell


But I have no idea how to run it against the column.

any thoughts.
0
Comment
Question by:rdefino
4 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36497687
Place this code in a general code module:

Public Sub TrimCells()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = LEFT(Cell,LEN(Cell)-3)
Next Cell
End Sub

Select the cells to edit and run the macro.

Kevin
0
 
LVL 5

Expert Comment

by:slycoder
ID: 36497716
In Excel - highlight a selection

1) go to the VBA IDE with Alt+F11
2) Create a module - Insert - Module
3) Create a Procedure - Insert - Procedure - "CutLast3Chars"
4) paste the code between Public sub ... and End Sub
5) while the cursor is also between the Pub sub and End sub, press F5

The module will be saved with this spreadsheet only

from then on - from the spread sheet - you can:
1) make a selection,
2) press Alt+F8
3) Click on CutLast3Chars and click on Run

Hope this helps.
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 125 total points
ID: 36501208
In a blank column alongside the data put formula:

=LEFT(A1,LEN(A1)-3)

Assuming text in A1, amend accordingly.

Copy down the 200 rows and then copy paste special values back over the original text. Delete formulas if so required.

Thanks
Rob H
0
 

Author Closing Comment

by:rdefino
ID: 36536613
Very easy, thanks!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now