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:

Please Login or Register  to view this content.

DonkeyOte,

Why doesn't this EVALUATE() construct work?

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

You might find it easier to use:

Please Login or Register  to view this content.

Originally Posted by DonkeyOte

You might find it easier to use:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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!