Other numerics is the cause of the issue. The earlier formula picks the text
string from the 1st numeric to 15 characters. Try the below formula which
will look for a "-" in the query string and pick the 4 numerics in front of
"-" to the end.


=VLOOKUP(TRIM(MID(A2,FIND("-",A2)-4,10)),Sheet2!A:B,2,0)

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


"djreason" wrote:


Additionally,

All of my descriptions also contain other numbers. I dont know if that
interferes with this function locating the numbers I want to manipulate.

hope this helps and thanks for the help!


"djreason" wrote:


Okay...

I got your suggestion to work when i created a brand new workbook with sheet
1 and sheet 2 as described in our short example.

However, now when I take that formula to my actual worksheet and make the
necessary changes to reference the correct cells and sheets, I get #N/A
again.

OUt of curiosity, what is the 15 in your function? Would that be important
if each description is different in length?


"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.