convert to number - suggestions for improving

[Terri Earley]

I work in a monster spreadsheet (45mb) with 6 tabs of linked data to an external database. I have an efficiency issue I am seeking advice on:

I have 20,000 rows of data that are updated weekly from the database and then filtered. From there, the data is copied and pasted (text) to another tab that uses Vlookup formulas on all the rows of data. the problem is that the Vlookup will not work until I have converted the pertinent column of data to numbers.... and I do this process this way: highlight first cell, Shift / end/ down to highlight all 20k cells, scroll back up to the first cell, right click the error box on the first cell and choose 'convert to number' and then wait like 40 minutes for the process to complete. Without this step, my vlookup will not work. Is there anything that I can do to make this faster? I feel like I am missing something because i have tried several things ... Thanks for the input.

[ConneXionLost]

Have you tried converting your lookup value to text?

[TMS]

If your lookup value is a text "number", use a double negative (--) to coerce the value into a true numeric.

Change, for example,

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Regards, TMS

[Terri Earley]

Oh thank you. I never heard of that trick. I have been struggling for years!! you have just saved me a ton of time! I love this site. Thanks.

[TMS]

You're welcome.