Hi,
Sorry i am not dat gud with formulas like you.
Why use 5,000 not 1,000?
Biz
=LOOKUP(5000,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5}))
Let's take a look and see how this formula works.
As far as I can tell the possible data configurations will be one of the following:
Name 2 digit year
Name 2 digit year|single letter
Name 4 digit year
Name 4 digit year|single letter
Johnson 73
Johnson 73a
Johnson 1973
Johnson 1973a
So, the year portion will be contained within the last 5 characters of the string.
What we do is find the position of the first digit in the string with this:
MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
Then, starting from that character, we tell it to step through that portion of the string and extract an increasing number of characters with this:
{1,2,3,4,5}
We know that the year number is within the last 5 characters of the string so we only need to look through the last 5 characters.
MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5})
Using that entire expression, we extract a series of TEXT substrings of increasing length until the year number is expossed. Like this:
A1 = Johnson 1973a
"1"
"19"
"197"
"1973"
"1973a"
This array of TEXT strings is then manipulated using the double unary minus "--".
--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5})
This will convert the TEXT numbers to numeric numbers and will generate errors for those strings that contain characters other than number digits.
--"1" = 1
--"19" = 19
--"197" = 197
--"1973" = 1973
--"1973a" = #VALUE!
So now we have this array:
1
19
197
1973
#VALUE!
Hey, we can see the year number in there. Now we have to get it out. Here's how we do that:
=LOOKUP(5000,{1,19,197,1973,#VALUE!})
In this application the way that LOOKUP works is it will return the *LAST* numeric value in the lookup array {1,19,197,1973,#VALUE!} that is less than or equal to the lookup value 5000. Since we're dealing with year numbers we know that we will never be anywhere near year 5000 so it is GUARANTEED that the lookup value 5000 will be greater than any numeric value in the lookup array.
The *LAST* numeric value in the lookup array {1,19,197,1973,#VALUE!} that is less than or equal to the lookup value of 5000 is 1973. The "cool" thing about this is that LOOKUP will ignore the errors in the lookup array (unless they are all errors).
So, to extract the year number from the string:
A1 = Johnson 1973a
=LOOKUP(5000,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5}))
Returns 1973
