Extracting a string and using it as the input for a VLOOKUP on a different sheet.

Hello,

I'll throw this out to see if anyone has seen this. I didn't find anything like it when searching the forum. Thanks in advance.

I'm trying to extract a value from a string in a cell and use it as the basis for a VLOOKUP on a different sheet.

For example:
Cell D63 on Sheet 1 contains "64500 - 54488 - Custom work"
Sheet2 A2 thru B300 contains the lookup table of custom projects and project owners
I need to be able to extract the "54488" from the string in D63 and use it as the lookup from Sheet2 and return the corresponding custom project owner.

I've been able to extract the "54488" using =SUBSTITUTE(MID(SUBSTITUTE(" - " & D63&REPT(" ",2)," - ",REPT(",",255)),2*255,255),",","")

I've been able to use =IFERROR(VLOOKUP(0+LEFT(D60,FIND(" ",D60,1)-1),Sheet2!$A$2:$B$300,2,FALSE),"") to lookup the project owner when the project number, 54488, is the first value in the string. But my input data is changing and the 54488 is no preceded and followed by " - ".....space dash space.

When I tried using SUBSTITUTE(MID(SUBSTITUTE(" - " & D63&REPT(" ",2)," - ",REPT(",",255)),2*255,255),",","") as the first parameter for the VLOOKUP, I get #N/A. I've confirmed that 54488 is in the table with a corresponding project owner.

Any ideas?

sample excel spreadsheet with example

FYI, all character got by using "Substitute" is TEXT format (not numerical value), you could do "trouble shooting" as follow

Try changing the first parameter of vlookup to following (if "n/a" not shown, that means all value in column A of sheet 2 is numerical value)
SUBSTITUTE(MID(SUBSTITUTE(" - " & D63&REPT(" ",2)," - ",REPT(",",255)),2*255,255),",","")-0

Text value (54488) from D63 of sheet 1 could also be obtained by (all character except space between 2 dash in D63)
=TRIM(MID(SUBSTITUTE(D63,"-",REPT(" ",500)),500,500))

Thanks. I'll play around with that over the weekend.