Find year in cell

AMAS · Jul 26, 2011

Hi,

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

Code:

=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

T. Valko · Jul 27, 2011

AMAS said:
Hi Biff,

I have been working on this all day, reviewing and tweaking along the way. Here is what I have so.

Code:

=IFERROR(--IF(IFERROR(LEN(IFERROR(MID(C2,FIND(19,C2),4),MID(C2,FIND(20,C2),4))),"")=4, IFERROR(MID(C2,FIND(19,C2),4),MID(C2,FIND(20,C2),4)), IF(LEN(LOOKUP(5000,--MID(C2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),{1,2,3,4,5})))=2, IF(LOOKUP(5000,--MID(C2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),{1,2,3,4,5}))>15, 1900+LOOKUP(5000,--MID(C2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),{1,2,3,4,5})), 2000+LOOKUP(5000,--MID(C2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},C2&"0123456789")),{1,2,3,4,5}))), "No date reported")),"No date reported")

I had to first limit the search for four-digit dates that started with 19 or 20 because I was getting wrong results when someone incorrectly writes a number that is not their birth date (e.g. 1234). Then I limited the second half to only two digit numbers to prevent it from getting these four or more digit numbers and thinking that it was year.

The problem that I still haven't resolved (not yet at least) is why these two scenarios are not working:

Code:

* Two-digit starting with zero (e.g. Johnson 07) * Study name begins with number not related to date (e.g. 907 Johnson 2007)

I will have to look at the formula more closely and test out some other tweaks.

Any suggestions are of course welcome.

AMAS

The extraction formula will strip off any leading 0s:

Johnson 07 = 7

I think we all assumed there was only one numeric substring within the string. The extraction formula will find the location of the first numeric substring.

907 Johnson 2007 = 907

I think we're going to have to start over!

How about posting several representative samples of data that show ALL the possible iterations that have to be considered.

T. Valko · Jul 27, 2011

Biz said:
Hi Biff,

Thanks again for your detail explanation.

Biz

You're welcome!

AMAS · Jul 27, 2011

Hi Biff,

The instructions tell people to input Last name followed by year. So the expected format should be:

Code:

Johnson 2009

Even so, in the sample I have that included a little over 40,000 entries, some people (as expected) don't follow instructions very well. So I have found:

Code:

Johnson '09

Code:

Johnson 09

Code:

Johnson, 2009

Code:

Johnson 2009a

Code:

2009 Johnson

Code:

572694423 Johnson 2011

This is probably their government ID or something (beats me)

And then of course some people don't provide any dates:

Code:

Mike Johnson

I have cleared all the other hurdles with the above formula with the exception of the trailling zero (e.g. 09) and the one with a non-year number in the begining.

I am using vba and so I can probably muster something using a loop, but I am trying to keep it as consise as possible using a formula, if possible.

Any suggestions are most welcome.

Thanks for looking into this. Its definately been a great learning experience for me.

AMAS

T. Valko · Jul 27, 2011

AMAS said:
Hi Biff,

The instructions tell people to input Last name followed by year. So the expected format should be:

Johnson 2009

Even so, in the sample I have that included a little over 40,000 entries, some people (as expected) don't follow instructions very well. So I have found:

Johnson '09

Johnson 09

Johnson, 2009

Johnson 2009a

2009 Johnson

572694423 Johnson 2011 This is probably their government ID or something (beats me)

And then of course some people don't provide any dates:

Mike Johnson

I have cleared all the other hurdles with the above formula with the exception of the trailling zero (e.g. 09) and the one with a non-year number in the begining.

I am using vba and so I can probably muster something using a loop, but I am trying to keep it as consise as possible using a formula, if possible.

Any suggestions are most welcome.

Thanks for looking into this. Its definately been a great learning experience for me.

AMAS

OK, the solution to this is very simple.

Name goes in one cell and the year number goes into another cell.

The cell for the year number has data validation set so that the user MUST enter a 4 digit year number from N to N.

AMAS · Jul 28, 2011

You are completely correct and I wish I could implement this. Unfortunately I'm too low down the food chain to make these orders. I just get the final dataset and am asked to analyse it according to different parameters (one of them being the year).

AMAS

T. Valko · Jul 28, 2011

AMAS said:
You are completely correct and I wish I could implement this. Unfortunately I'm too low down the food chain to make these orders. I just get the final dataset and am asked to analyse it according to different parameters (one of them being the year).

AMAS

How about this...

Extract the user entered year to an intermediate cell in column B. Then you can "refine" it using a much smaller formula in column C.

Book1

A

B

C

2

Johnson 2009

2009

3

Johnson '09

9

2009

4

Johnson 09

9

2009

5

Johnson, 2009

2009

6

Johnson 2009a

2009

7

572694423 Johnson 2011

2011

8

Mike Johnson

#N/A

_

9

123 Johnson 1988a

1988

10

Johnson 88

88

1988

11

Johson 88b

88

1988

12

123 Johnson 88

88

1988

13

123 Johnson '88a

88

1988

Sheet1

T. Valko · Jul 28, 2011

T. Valko said:
How about this...

Extract the user entered year to an intermediate cell in column B. Then you can "refine" it using a much smaller formula in column C.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 172px"><COL style="WIDTH: 46px"><COL style="WIDTH: 46px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Johnson 2009</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2009</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Johnson '09</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Johnson 09</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Johnson, 2009</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2009</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Johnson 2009a</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2009</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">572694423 Johnson 2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Mike Johnson</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">#N/A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">123 Johnson 1988a</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1988</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1988</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Johnson 88</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">88</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1988</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Johson 88b</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">88</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1988</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">123 Johnson 88</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">88</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1988</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">123 Johnson '88a</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">88</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1988</TD></TR></TBODY></TABLE>

I forgot to include the formula for column B.

=LOOKUP(5000,--MID(RIGHT(A2,5),MIN(FIND({0,1,2,3,4,5,6,7,8,9},RIGHT(A2,5)&"0123456789")),{1,2,3,4,5}))

bosco_yip · Jul 28, 2011

mvptomlinson said:
To extract the numeric values in the string,
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))
.

The formula can save this part of .... ("0"&…., and like this :

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

T. Valko · Jul 28, 2011

bosco_yip said:
The formula can save this part of .... ("0"&…., and like this :

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

See reply #11:

http://www.mrexcel.com/forum/showpost.php?p=2804344&postcount=11

AMAS · Jul 28, 2011

Thanks. I like this approach. Its simple, but effective. I will run it through the whole database and see if I hit any snags, but so far its on target.

Thanks again.

AMAS

Find year in cell

AMAS

Active Member

T. Valko

Well-known Member

Excel Facts

T. Valko

Well-known Member

AMAS

Active Member

T. Valko

Well-known Member

AMAS

Active Member

T. Valko

Well-known Member

T. Valko

Well-known Member

bosco_yip

Well-known Member

T. Valko

Well-known Member

AMAS

Active Member

Similar threads

Forum statistics

Share this page

Find year in cell

Active Member

Well-known Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock