Need to find a string of values on a cell and if found then write on colum

**ramosja** · 04-24-2013, 03:31 PM

Hello, need to convert a number into a year format. My cell contains for example 02-05 and this means years from 2002 to 2005. I would like to use a formula to identify several patterns and write the answer into columns. This is how I would write it, I know is not correct.

if A1 contains 02-05, then write on B1 2002,2003,2004,2005. My ideal world would be to write into new columns, but if written as text into a single column, then I can extract.

Thanks,

Javier

**ChemistB** · 04-24-2013, 03:42 PM

Will they always be at least the year 2000?

**ChemistB** · 04-24-2013, 03:48 PM

In B1 dragged to the right (you might need to reformat to General)
=IF(LEFT($A$1,2)+COLUMN(A1)>(RIGHT($A$1,2)+1),"",(20 &LEFT($A$1,2))+COLUMN(A1)-1)
Does that work for you?

**ramosja** · 04-24-2013, 04:07 PM

Hello, well actually no, the years are all over the place its ranges from 73-2013 (1973-2013) and part number lines are like this:

FENDER LH 07-11 SD

Thanks

**ChemistB** · 04-24-2013, 04:22 PM

Ahhh, so now a bit more difficult. and sometimes it has the full year (i.e. 73-2013) and sometimes it doesn't?!?

**ramosja** · 04-24-2013, 04:41 PM

Hello, no it never has the complete year just 73-13 for example

**ChemistB** · 04-25-2013, 02:06 PM

See if this works for your data,
First, to keep the formulas from getting too long, I extracted the beginning and ending dates in B and C
in B1
=IF(TRIM(MID(A1,SEARCH("-",A1)-2,2))+0>20, 1900+TRIM(MID(A1,SEARCH("-",A1)-2,2)), 2000+TRIM(MID(A1,SEARCH("-",A1)-2,2)))
In C1
=IF(TRIM(MID(A1,SEARCH("-",A1)+1,3))+0>20, 1900+TRIM(MID(A1,SEARCH("-",A1)+1,3)), 2000+TRIM(MID(A1,SEARCH("-",A1)+1,3)))
Then in D1 copied to the right
=IF($B$1+COLUMN(A1)>$C$1+1,"",$B$1+COLUMN(A1)-1)
See example