Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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, |
#2
|
|||
|
|||
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, | |
#3
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell has an apostrophe and is text | Excel Discussion (Misc queries) | |||
Adding contents of one cell to a range of cells. | Excel Worksheet Functions | |||
Delete apostrophe | Excel Discussion (Misc queries) | |||
adding only positive numbers | Excel Discussion (Misc queries) | |||
Add apostrophe | Excel Worksheet Functions |