Excel macro to change text to numbers in a column

[imperialzinc]

Hello,

I have a query that pulls up a lot of information and puts it all into a sheet in my workbook. All of those sheets are formatted in text rather than numbers. I have a spreadsheet set up to do vlookups based on numbers that I type in. The vlookups dont work on plain text only on numbers. So I need to convert all of the text in a specific sheet to numbers. I can just do text to column on all of the columns and it fixes it, but the person who will use the spreadsheet isn't very knowledgeable with excel. Can I add a command like that to my query? Or maybe I can get a macro that activates when refreshing the query? I pasted my query below.

Please Login or Register  to view this content.

[imperialzinc]

Ok so I just wanted to try to replace all of the text where it says "text" and i put "number" in it's place. How do you paste a query? JK it doesn't work because some of the columns have words in them and they cant be converted.

[PCI]

Please attach a Excel sample file and detail clearly which columns to convert

[mikerickson]

If you have a column of numerals (strings rather than numbers) you can convert them to numbers using TextToColumns.

[imperialzinc]

I just recorded a macro that does text to column for each row in the spreadsheet. I wish I could just have the query mark numbers as numbers and text as text, but at least I have it working for now.