A formula cannot change other cells, it just returns a result to the cell it
is in. Maybe you meant a macro? This works on the range selected .

Sub DoConvertToText()
Dim Cell As Range
Dim Counter As Long
''Special cell works on the entire current region if only one cell is
selected
If Selection.Cells.Count = 1 Then
If ActiveCell.Value < "" And Left(ActiveCell.Formula, 1) < "="
Then
ActiveCell.Value = "'" & ActiveCell.Value
MsgBox "One cell converted to text"
End If
Else
On Error GoTo NoCells
For Each Cell In Selection.SpecialCells(xlCellTypeConstants)
Cell.Value = "'" & Cell.Value
Counter = Counter + 1
Next
MsgBox Counter & " cells were converted to text"
End If
Exit Sub
NoCells:
MsgBox "No cells with constants were found"
End Sub

--
Jim Rech
Excel MVP
"Jeff" wrote in message
...
|I need help to run a formula that would replace the value of the cells
| K10:K500 by adding an apostrophe ' in each row in column K
|
| Example row K9: is 01/20/2005 needs to be '01/20/2005.
|
| Regards,
|