VBA to convert text to values

keith · May 13th, 2002, 10:24 AM

I have a database that drops data into a spreadsheet column. When the data comes in it is all values but in text format 3,5,25,1 etc.. When I sum the cell values it returns 0 instead of adding the values because it sees them as text. What I need is a macro I can run to go down the entire colum and convert from text to real numbers.. any help??

Mark W. · May 13th, 2002, 10:29 AM

Why not select your values, choose the Data | Text to Columns... menu command, and press [ Finish ]?

keith · May 13th, 2002, 10:35 AM

I can try to record a macro that does that.. but I am not the user of the sheet so I have to get it done automatically when the user refreshes the database query..

Mark W. · May 13th, 2002, 11:32 AM

Why don't you change the database query so it returns numeric data instead of text data?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 10:24 AM	#1
keith Board Regular Join Date: Mar 2002 Posts: 88	I have a database that drops data into a spreadsheet column. When the data comes in it is all values but in text format 3,5,25,1 etc.. When I sum the cell values it returns 0 instead of adding the values because it sees them as text. What I need is a macro I can run to go down the entire colum and convert from text to real numbers.. any help??

May 13th, 2002, 10:29 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Why not select your values, choose the Data \| Text to Columns... menu command, and press [ Finish ]?

May 13th, 2002, 10:35 AM	#3
keith Board Regular Join Date: Mar 2002 Posts: 88	I can try to record a macro that does that.. but I am not the user of the sheet so I have to get it done automatically when the user refreshes the database query..

May 13th, 2002, 11:32 AM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Why don't you change the database query so it returns numeric data instead of text data?