I find that the expression you provided works if the last part of my
descriptions end in the short year ranges. However, in ALL of my
descriptions, the short year ranges can be found anywhere within a
description that is in excess of 1000 words.

"Jacob Skaria" wrote:

Test this with Sheet1 and Sheet2 and a similar example as posted and if that
is working fine you can build on .

If this post helps click Yes
---------------
Jacob Skaria


"djreason" wrote:

I slightly modified your suggestion to match my documents

=VLOOKUP(MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C 2&"0123456789")),15),'[Monroe Missing links and price (2).xls]Sheet3'!A:B,2,0)

This returns a value of #N/A all the way down my entire list of products.

Am I wrong or do I not see a replace function in there somewhere?



"Jacob Skaria" wrote:

With entries like the below in Sheet1 in Col A

Col A
fits years 1995-98
fits years 1997-98

and with entires like the below in Col A/B in Sheet2

Col A Col B
1995-98 1995, 1996, 1997, 1998
1997-98 1997, 1998

try the below formula in Sheet1 cell B2

=VLOOKUP(MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A 2&"0123456789")),15),Sheet2!A:B,2,0)


If this post helps click Yes
---------------
Jacob Skaria


"djreason" wrote:

I have a large file that contains long product descriptions in one workbook
file in say Column C. Within those descriptions, there are year ranges. for
example, a description may say "fits years 1995-98". These descriptions
change for every product I am listing. What I need to do is change the
1995-98 to read 1995, 1996, 1997, 1998 instead.

On a second workbook, I have listed all of the possible shortened year
ranges (1995-98) in column A. In Column B, I have the expanded year ranges
(1995, 1996, 1997, 1998).

My question is, how can I write a function statement that looks line by line
through Column C on Worksheet 1 for the dynamic shortened year ranges, then
looks for the same value from column A worksheet 2 and replaces with the
expanded year ranges from column B on worksheet 2?

I really dont want to have to do a replace individually line by line.

Thanks in advance for any help.