Forumla not calculating because of cell format??

[KarlMc]

H
I am writing a macro and am encountering a very annoying problem. I start with a column of numbers (product codes), in the next column I paste a formula to get the first two digits of this code number. In the following column I paste another formula that takes the result of the last formula and uses a VLOOKUP to find the two digits in a table on another sheet and return the product group (text) the product is in.
The second formula gives a result of #N/A but if I double click in the cell with the two digits it will work. I even tried formatting the column all to numbers and I tried copying the result of the formula and paste special as values
I realise this may be confusing so here are the formulae and a sample of data

Formula1 =IF(MID(M1,1,4)=""2415"",MID(M1 ",1,4),MID(M1,1,2)
Formula2 =VLOOKUP(N1,'Product Groups'!$A$1:$B$13,2,FALSE

Prod Code Forumla1 Result Formula2 Resul
2445202 24 #N/

Any help to get around this problem without having to hard-code the product groups into the VBA code would be greatly appreciated

Thanks

 

[Gord Dibben]

Karl

Format the range of cells to Number. \

Copy an empty cell.

Select the range of cells(which appear to be text in your case).

Paste Special>Add>OK>Esc.

This should force the format to numeric.

Gord Dibben Excel MVP