vlookup to ignore ## in a cell

hi,

how i can vlookup the below';

"##gbws-vm111"

but in the other spreadsheet it states it as: "gbws-vm111"

I want the vlookup to ignore the ## .

hows can i do the above.

thank in advance.

Basically in spreadsheet 1 i have the following:

Column A1 = ##gbws-vm111
column B1 = VLOOKUP......THIS IS WHERE THE FORMULA AND ANSWER WOULD GO---the answer would be from spreadsheet 2 column B = powered on.

Spreadsheet 2 has the following:

Column A1 = gbws-vm111
column B1 = powered on


I hope im not to confusing. how would i do the above

thanks again

that didnt work.

the lookup is not in sheet 2, its in a new spreadsheet.

i used the below formula and it doesnt want, the answer is #N/A


=VLOOKUP(SUBSTITUTE(A1,"#",""),'[199 VDI Kitlist Master (version 1).xls]199 Kitlist VDI VSI'!$A:$C,3,FALSE)

sorry its worked. my mistake