Software

Ask something
Question Locked

Truncating a cell

0 helpful
3 replies

by william.white

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I want to be able to reference a cell that is
formatted with a four digit year into another cell, but have the destination cell truncate the value so only the last two digits of the year show.

For example, cell A1 is 2008, and I?m referencing that value in cell E47. I only want 08 to appear in cell E47. Then if I change cell A1 to 2009, I want cell E47 to show 09.

Is that possible, and if so can you rescue me?
I haven?t been able to find anyone who can do this, and I am far from an Excel user.

Thanks so much in advance.

3 replies

by jclemenger

If the entry cell is formatted as text, the MID function will give you as many characters as you want starting from any point in the cell.

In the example cell E47 should contain this
=MID(A1,3,2)

If the entry cell is formatted as a date, I don't know a way to restrict the characters showing in the target cell.

by tobif

If A1 contains a date:
=TEXT(A1,"YY") (this gives a string as result)

If A1 contains the value 2008,
either a string operation:
=RIGHT(TEXT(A2,"00"),2)

or a mathematical one:
=MOD(INT(A1),100) (For this one, you need to format the cell to show two digits)

By the way, if the value in A1 is a date, then you COULD use this:
=A1
And then format the cell to show just "YY"!
It would still maintain the full date value, but you'd simply adjust the presentation.

For all these examples, adjust for localization. (My installation wants semicolon instead of comma, and the letter for year is different in Swedish...)

This discussion is closed to new replies.