Converting Text to number issue

Hi,

I am currently trying to find an easy way to convert cell references to number format.

The issue I'm having is when compiling a small formula (CONCATENATE) as part of a macro in order to produce a unique reference number to a column of data (1000's of cells long) I come accross the age old number stored as text issue after the macro has converted the formula to values.

Have tried editing the macro to number stored as text = false which removes the annoying little red triangle but the vlookup functions still wont read the values. My current solution is to highlight the entirity of the data and convert to number which takes a long long time as there are multiple lookups assigned to the cells, which for my current project is not acceptable.

Am looking for a quick solution, (with no interaction required) something that would convert the cells to the format required either before the formula is assigned and overwritten by values or just after. Currently I'm stumped.

Any help most welcome and appreiciated!

use vlookup with &""

=VLOOKUP(D1&"",A:B,2,FALSE) which will look up text from a real number in D1
or if it's the other way around
use vlookup with +0

=VLOOKUP(D1+0,A:B,2,FALSE)
which will look up a real number from text in D1