Find year in cell

[AMAS]

Hi,

This formula needs a tweak and I can't seem to get it right:

=IF(FIND({90,91,92,93,94,95,96,97,98,99},A2),{1990,1991,1992,1993,1994,1995,1996,1997,1998,1999},"No year found")

It looks for the two digit year in a cell and returns its four digit year format. It only seems to work if the first year (e.g. 90) is the first option in the Find. If I put for example {89,90} then it gives me a #value error.

AMAS

 

[Joe4]

Wouldn't it just be easier to use:
=A2 + 1900

 

[Jonmo1]

What is in A2 ?

Is it a Date, or a Text String that looks like a date?

What does this return
=ISNUMBER(A2)

 

[AMAS]

Hi,

Sorry I wasn't clear. A2 contains a name plus a year (or it should anyways). Therefore it should be something like:

Johnson 1973

or

Johnson 73

When there are more than one Johnson then it becomes something like:

Johnson 73a

AMAS

 

[AMAS]

I have been able to capture the four digit date since it has to start with 19 or 20, but can't get find a way to capture the two digit date.

Any suggestions?

AMAS

 

[mvptomlinson]

Assume list of names/numbers in A1:A10.

To extract the numeric values in the string, you could use this in B1 filled down:

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Then in C1 filled down:

=B1+1900*(LEN(B1)=2)

You could do it in one cell, it would just make the formula in B1 thrice as long as you'd have to repeat the LOOKUP function for each argument in the IF statement.

 

[AMAS]

This is great. Thanks.

AMAS

 

[AMAS]

I am trying to understand the logic behind it, but can't figure out half of it. Could you please elaborate on your technique so I can learn to use it for future problems?

Thanks in advance.

AMAS

 

[mvptomlinson]

Take a look at this thread (where I borrowed oldchippy's formula from post #6).

http://www.excelforum.com/excel-wor...-extract-number-from-alphanumeric-string.html

Below that there is a link to an alternative formula (array formula), as well as an explanation of how the formula works from "daddylonglegs". There's also a tip about using Excel's 'Evaluate Formula' toolbar option to walk through any formula step-by-step.

 

[AMAS]

Perfect. I will read up and be back if you I am still stumped.

AMAS