Cell Formatting not working - breaks INDEX/Match

I am trying to build a template for testing and I have to use about 95000 zipcodes. this is in it's own sheet (say, zipcodes)

I am predominently using index/match to look up values but because the zipcode column is in text format to preserve the leading zero's I always get a #N/A error because the format in the zipcode cell; in the results sheet is in number (special custom) format.

My solution is to convert the 95000 zipcodes to number format.

I have tried the following and it hasn't worked:
- highlighted the column (all cells) - changed format to number. But when i use the =istext function against any of these cells, the outcome is "true", as in "it's still text format"
- copied the entire zipcode column, pasted it to another column formatted as a number. Pasted special by keeping number format. This didn't work

The only that does work is if I:
- Convert the column to number format, double click on each cell - but this is not viable to me as I have large amount of data

Is there another way where I can mass the change format of this zipcode column from text to number?

Thanks in advance

Highlight the column with the zipcodes in, click on Data | Text-to-columns, then click Finish.

Note that you will loose leading zeros, and if these are significant it may be better to amend your formula so that you do not need to change the zip codes. Post an example of the formula that you are using.

Hope this helps.

Pete