Excel 2007 : Extracting the year from a string

[robbyvegas]

Hello everyone,

I have a column that is in bad shape. It is full of alpha numeric text, including a date. (e.g. 63 Fed. Reg. 47319 (Sept. 4, 1998)). I can't count on the size of the cell always being the same, nor that the date is displayed as shown above in parentheses. Occasionally, the dates are displayed like this (3/9/95). I need to create a new column that contains the year. I fully expect that I may need to run a couple of different formulas to gather the 4 digit years and the 2 digit years. Any ideas on how to do this?

Thanks in advance.

Rob

[rwgrietveld]

It would help if you specify the range of the years (1990 -2010?)

[robbyvegas]

It would help if you specify the range of the years (1990 -2010?)

Sure. the dates listed will range from 1970-2009. I'd like to get them all, but would be happy to make this work for 1998-2008.

Thanks again.

[robbyvegas]

Does anyone have any suggestions on this?

[shg]

I suggest you post a workbook containing text strings that illustrate the gamut of formats you need to accommodate.

[TriciaMcMillan83]

I suggest you post a workbook containing text strings that illustrate the gamut of formats you need to accommodate.

I agree, that would definitely help.

Here you have a sample of some code, but bear in mind that it has only been tested with the following data, and only while searching for years starting with "19xx"

63 Fed. Reg. 47319 (Sept. 4, 2010)
63 Fed. Reg. 47320 (Sept. 4, 1998)
63 Fed. Reg. 47376 (Sept. 4, 1976)

Please Login or Register  to view this content.

It returns:

63 Fed. Reg. 47319 (Sept. 4, 2010) Year not found
63 Fed. Reg. 47320 (Sept. 4, 1998) 1998
63 Fed. Reg. 47376 (Sept. 4, 1976) 1976


Bear in mind that the code has a bug: If there is more than one place where "19" figures, then it will just take that one, and doesn't check if there's any other.
The code fails, if you have data like 19 Fed. Reg. 47367 (Sept. 4, 1987), because the first time "19" appears the next two characters are not numeric.

But I hope you get the idea of what can be done.

Regards,

TriciaMcMillan

[DonkeyOte]

If the dates as you say aren't always in parenthesis, aren't always the same no. of digits etc then perhaps making the one assumption that the year is always the last numeric value in the string perhaps:

Please Login or Register  to view this content.

So if say A1:A5 contain:

63 Fed. Reg. 47319 (Sept. 4, 1998)
63 Fed. Reg. 47319 (3/9/95)
63 Fed. Reg. 47319 Sept. 4, 1998
63 Fed. Reg. 47319 (3/9/04)
63 Fed. Reg. 47319 Sept. 4, 2002

Then with

Please Login or Register  to view this content.

B1:B5 would return 1998, 1995, 1998, 2004, 2002 respectively.

If the above still falls foul of some of the string variations then you will definitely need to post a file with more detailed examples.

[DonkeyOte]

I was just looking at this again to see if there would be a viable formula approach...

If we make the same assumption that the year is the last numeric in the string and use the same example strings as prior post (A1:A5) then:

Please Login or Register  to view this content.

would return the associated year value...

NOTE: the above would not work pre XL2007

However unlike the UDF it would not adjust for 2 digit output - so you would need to account for that separately eg:

Please Login or Register  to view this content.

where B1 holds output of the earlier monstrosity.

That said I'm sure the likes Ron C, daddylongs, shg etc could come up with something really clever ... (challenge)


Again, all moot if year is not always the last numeric.

[martindwilson]

i think that shg has indicated the first starting point!