Deleting last two characters in a cell

Everyone,

I would really be grateful if someone could help me with the following:

In a column I have several rows (on average 100 rows) in which values like the following are defined: EP10000001A1. Thus, it looks something like the following

EP10000001A1
EP10000002A1
EP10000003A1
EP10000004A1

I would like to delete the last two characters in ever cell for every row. Hence, taking the above example all "A1" should be deleted so that it should looks like this:

EP10000001
EP10000002
EP10000003
EP10000004

As I said, on average I have a 100 rows in a column (sometimes up to 300 rows). Hence, I would really like to avoid doing this manually. How can I achieve this? Is there a standard Macro for this type of operation? (I must admit that I am new to VBA/creation of Macros).

Thank you in advance.

Best regards,
tony

Try this:

Sub TruncateColumn()
Dim LR As Long, LC As Long, vCol As Long

vCol = 1    'column to evaluate... A=1, B=2, etc
LR = Cells(Rows.Count, vCol).End(xlUp).Row

Application.ScreenUpdating = False
    With Range(Cells(1, Columns.Count), Cells(LR, Columns.Count))
        .FormulaR1C1 = "=LEFT(RC1,LEN(RC1)-2)"
        .Copy
        Cells(1, vCol).PasteSpecial xlPasteValues
        .ClearContents
    End With
Application.ScreenUpdating = True
End Sub

DonkeyOte,

Why doesn't this EVALUATE() construct work?

Sub TruncateSelection()
'Select a range and then run the macro to truncate values without looping
Dim Cell As Range

    With Selection.Cells
        .Value = Evaluate("IF(ROW(1:" & Selection.Cells.Count & "),LEFT(" & .Address & ", 12)")
    End With

End Sub

Don, nevermind, I figured out my parens placement problem, again.

Tony: use this version if you want to select any range and run the macro, it will trim 2 characters off the values regardless of length.

Sub TruncateSelection()
'Select a range and then run the macro to truncate values without looping
Dim Cell As Range

    With Selection.Cells
        .Value = Evaluate("IF(ROW(1:" & Selection.Cells.Count & "),LEFT(" & .Address & ", LEN(" & .Address & ")-2))")
    End With

End Sub

JB, glad you resolved !

What I often do if I'm not getting desired results is use Debug.Print on the Evaluate string to see what I'm generating, eg:

Debug.Print "IF(ROW(1:" & Selection.Cells.Count & "),LEFT(" & .Address & ", 12)"

I find that generally allows you to identify function errors quite quickly.

I would still say though that if you opt to work with a Selection you should be sure to iterate by Area to handle possibility of non-contiguous selection at run time.

On an aside instead of:

"IF(ROW(1:" & Selection.Cells.Count & ")

You might find it easier to use:

"IF(ROW(" & .Address & ")

Originally Posted by DonkeyOte

You might find it easier to use:

"IF(ROW(" & .Address & ")

I get errors on that. When I use your debug suggestion, I get this:

IF(ROW($A$7:$A$8),LEFT($A$7:$A$8, LEN($A$7:$A$8)-2)

...as opposed to this, which works:
IF(ROW(1:2),LEFT($A$7:$A$8, LEN($A$7:$A$8)-2)

As I see it your last Evaluate is still missing a closing parenthesis, eg:

.Value = Evaluate("IF(ROW(" & .Address & "),LEFT(" & .Address & ", LEN(" & .Address & ")-2))")

Hi Tony,

You can do this by entering 3 formulas,

please find the image i attached for your reference.

hope this will help you.


Regards,
Rajesh

Rajesh, why 3 functions - as already illustrated by JB (via Evaluate) the same can be achieved with one:

=LEFT(cell,LEN(cell)-2)

On an aside please do not load your posts with a blog link - put this is in your signature if you want / need to.

DO, that would be in conflict with FR # 13
13. Cross promotion/Links of any competitor forums to www.excelforum.com in signature of any member are NOT allowed.

From what I can tell it's not exactly a competitor...

DO, Must agree

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

From what I can tell it's not exactly a competitor...

must agree it seems every TD&H in certain locations have reinvented the wheel!