Adding an apostrophe
 

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,

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,
|

With your present dates in K1:K500, use a helper column , say column J and
put this formula in J10 and copy down.........then do Copy PasteSpecial
Values on column J and then copy and paste it over to column K to replace
your dates without the apostrophe.

="'"&MONTH(K10)&"/"&DAY(K10)&"/"&YEAR(K10)

Vaya con Dios,
Chuck, CABGx3


"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,