On Saturday 26 September 2009 06:27:10 Wade Smart wrote:
<<<SNIPPED by Pradeep>>>
> Ok. Anyway I can do this without having to add in this extra column
> each time I do the sheet?
>
> Wade
>
Wade,
Yes, you can.
Just because it is a Saturday morning in India, and because I was
totally unemployed, I thought I would take a stab at doing it, and
here is what I came up with:
Assuming that the initial sequence of digits and hyphens are in A1,
put this into B1 (and copy down to other cells in Column B if you so
desire):
=YEAR(NOW())-(REPLACE(REPLACE(A1;SEARCH("-[0-9]{3,}-[0-9]{3,}-[0-9]
{4,}$";A1);15;"");1;SEARCH("[0-9]{2,}$";REPLACE(A1;SEARCH("-[0-9]
{3,}-[0-9]{3,}-[0-9]{4,}$";A1);15;""))-1;"")+1900)
For the sequence 265879-09-24-71-918-333-0055, I get the answer 38,
which is more or less (I think) what you want.
It works: but, the question is, do you *really* want to write, and
debug the statement above, which, in any case, is hard-coded for the
specific problem you have mentioned in your original post ?
In any case, have fun figuring out the formula :-^) !
Hope this helps in some small way.
Pradeep Srinivas
--
on 26 Sep 2009, Saturday, IST (which is GMT +05:30)
-----------------------------------------------------------------------
Do not confuse motion and progress. A rocking horse keeps
moving but does not make any progress.
-- Alfred A. Montapert
-----------------------------------------------------------------------
Signature block is /auto/ /generated/ from the Unix utility *fortune*
fortune run at 10:10 IST on 26 Sep 2009, Saturday
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
