Text to Value
 

Hi. I have a column of numbers. Before my code executes, I would like to
check the column to make sure all numbers are truly numbers. Every so often
a number slips in with a ' in front of it, making it text. Is there a
simple way to remove the ' in cells where it occurs? I tried to use
edit/replace, but MS said it could not find the '. Thanks!

Text to Value
 

You could do this in your code, if you want, e.g.
FixCell = Val(Trim(ActiveCell.Value))


"Steph" wrote:

Hi. I have a column of numbers. Before my code executes, I would like to
check the column to make sure all numbers are truly numbers. Every so often
a number slips in with a ' in front of it, making it text. Is there a
simple way to remove the ' in cells where it occurs? I tried to use
edit/replace, but MS said it could not find the '. Thanks!

Text to Value
 

Good evening Steph

This routine should get rid of the annoying ' and leave "proper"
numbers and formulae alone:

Sub TextNumber()
On Error Resume Next
For Each usrcell In Selection.Areas
usrcell.FormulaLocal = usrcell.FormulaLocal
Next
End Sub

Highlight the whole range(s) you want to check and then call the
macro.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=381362

Text to Value
 

Steph wrote:
Hi. I have a column of numbers. Before my code executes, I would like to
check the column to make sure all numbers are truly numbers. Every so often
a number slips in with a ' in front of it, making it text. Is there a
simple way to remove the ' in cells where it occurs? I tried to use
edit/replace, but MS said it could not find the '. Thanks!

This happens often to me too. To ensure numbers, use the paste special
funtion to add a zero (yes, 0) to all of the suspect cells. This
converts all to real numbers.