Weird issue with vlookup

[spanspace]

Ok so when I point the vlookup in F9 to B9 I get a big fat #n/a. If the information in b9 was just the number and the the left formula it would work. I need help resolving this. I am sure its something easy..but my mind is moosh already from other projects.

Excel Workbook
	A	B	C	D	E	F	G
8	UNIQUE ID	ID	Agent Name	Role	Site IBADE	Site	Agent ID
9		8886	Add Agent to Master	#N/A	#N/A	#N/A	8886
WTD Conversion

Excel 2007

Cell Formulas
Range	Formula
A9	=IF(C9="Add Agent to Master","",CONCATENATE(B9," ",C9))
B9	=LEFT(G9,4)
C9	=IF(ISBLANK(G9),"",IF(ISNA(VLOOKUP(E9,Agents!$I$2:$L$600,4,FALSE)),"Add Agent to Master",VLOOKUP(E9,Agents!$I$2:$L$600,4,FALSE)))
D9	=IF(A9=" ","",IF(ISBLANK(A9),"",VLOOKUP(A9,Agents!$C$2:$DE$640,MATCH("Role",Agents!$C$2:$DE$2,0),FALSE)))
E9	=IF(ISBLANK(G9),"",CONCATENATE(F9," ",LEFT(G9,4)))
F9	=VLOOKUP(B9,Agents!$A$7:$F$640,MATCH("Site",Agents!$A$7:$F$7,0),FALSE)

 

[Jonmo1]

Change the formula in B9 from

=LEFT(G9,4)
to
=LEFT(G9,4)+0

 

[spanspace]

Same result

 

[Gerald Higgins]

What about right(g9,4)+0

 

[nigelk]

=VALUE(LEFT(G9,4))

Might do the trick.

 

[spanspace]

I tried all that before and it doesnt work.

 

[spanspace]

I figured it out. Unfortunately the idea wontwork out for me in the long run. I have a similiar issues I am going to post as seperate thread.

 

[Gerald Higgins]

Can we be absolutely clear please ?
What exactly is in G9 ?
Is it just the number 8886, or are there any other characters, including spaces, either before or after the number ?
And which cell(s) currently contain(s) the #N/A ?

I've tried replicating this and it works for me with the solution posted by jonmo1 in his first post, but I may be missing something.

 

[Gerald Higgins]

Oops, our posts crossed. So what was the solution, just out of interest ?