changing from character to value format

pjp · Apr 10th, 2002, 02:16 PM

I have a file with data that came from another source (I don't know where). The problem is that the numbers are not displayed as values. But if I hit edit (F2) and then hit enter, the cell contents change from a character to a value. However, I need to do this for about 4000 lines of data. I am not that familar with macros, but I was able to create a macro to perform that sequence, but how do I make it repeat it for 4000 rows and then stop?

Asala42 · Apr 10th, 2002, 02:38 PM

Hiya,

I'm still fairly new to VBA but I have what might be another solution to your problem.

Type a 0 in a cell already formatted as a number, copy that cell, select the range of imported numbers, editpaste specialadd. That should automatically convert the range to numeric data (instead of text).

Hope that helps,
Adam

pjp · Apr 10th, 2002, 02:55 PM

It worked! Thank you so much!

Mark W. · Apr 10th, 2002, 03:31 PM

You could have also used the Data | Text to Columns... menu command. And, BTW I wouldn't use "format" in this context. Instead, I'd use "data type" which includes: numeric, string (or text), boolean, and error.

[ This Message was edited by: Mark W. on 2002-04-10 14:37 ]

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

Apr 10th, 2002, 02:16 PM	#1
pjp New Member Join Date: Apr 2002 Posts: 2	I have a file with data that came from another source (I don't know where). The problem is that the numbers are not displayed as values. But if I hit edit (F2) and then hit enter, the cell contents change from a character to a value. However, I need to do this for about 4000 lines of data. I am not that familar with macros, but I was able to create a macro to perform that sequence, but how do I make it repeat it for 4000 rows and then stop?

Apr 10th, 2002, 02:38 PM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,030	Hiya, I'm still fairly new to VBA but I have what might be another solution to your problem. Type a 0 in a cell already formatted as a number, copy that cell, select the range of imported numbers, editpaste specialadd. That should automatically convert the range to numeric data (instead of text). Hope that helps, Adam

Apr 10th, 2002, 02:55 PM	#3
pjp New Member Join Date: Apr 2002 Posts: 2	It worked! Thank you so much!

Apr 10th, 2002, 03:31 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	You could have also used the Data \| Text to Columns... menu command. And, BTW I wouldn't use "format" in this context. Instead, I'd use "data type" which includes: numeric, string (or text), boolean, and error. [ This Message was edited by: Mark W. on 2002-04-10 14:37 ]