MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Vlookup problem when number is formatted as "special"

Posted by KELLY J on February 14, 2002 8:40 AM

i think i need to try to be a little more clear with my question. i am trying to do a vlookup and the column that it is using is formatted as "special", the vlookup will not recognize the number in that column until i change the format on that column to "text" and then retype the number by hand. why is it doing this and how do i fix it without having to retype an entire column of numbers ?

Posted by Aladin Akyurek on February 14, 2002 9:52 AM

Try what follows:

Insert a new column next to the one with Special format.

Lets say the latter is column A and the Special formatted entries (I assume Zip Code) start in A2.

In B2 enter: =(A2&"")+0

Give a double click on the little black square on the lower right corner of B2.

Make a copy of your WB before applying the following step.

You can copy B-cells and do a Paste Special >Values in B, then delete column A.