On Mon, 13 Mar 2006 06:12:08 -0500, Den wrote:



Hope I understand your data. Thinking a little more about this, if the
constant is the "E:" bit in the string, then it would be smarter to
use that. Hence:

=LEFT(A1,FIND("E:",A1)-1)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW(1:255),1),0))),Sheet1!A2:B5,2,FALSE)

is a more general solution and avoids having to worry about whether
there is a "\" or not.

Hope this gives you what you want.



Thank Richard for your help. I really appreciate.

Right now it doesn't work but you've given me hope that it can be
done.

I've tried to copy the formula into excel but I have no results with
CTRL+SHIFT+ENTER ; never use this combination of keys.

The formula just "sits" there in the cell and nothing appends;

I suspect that maybe I wasn't clear in my description of my data and
that some minor corrections may be needed.

I will have to struggle since this is beyond what I ever did before
with Excel.

Den

Hi Den,

I'm sending an email with a small Excel workbook attached which
contains your sample data and the formula above.

I've just realised that I only tested it on one file name on row 1 and
that I should have made the end bit of the formula absolute so that
the "1:255" doesn't change when copied down.

It should have read

=LEFT(A1,FIND("E:",A1)-1)&VLOOKUP((RIGHT(A1,MATCH("\",MID(A1,LEN(A1)-ROW($1:$255),1),0))),Sheet1!$A$1:$B$5,2,FALSE)

If you paste that you will probably need to immediately edit it with
F2, and then hold the CTRL and Shift keys down whilst you Enter it.
You should end up with curly brackets {....} at either end of the
formula, which will confirm that it is correctly entered as an array
formula.

Rgds



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________