FIND and REPLACE ' apostrophe

J

James T

I have a spreadsheet that has a column of functions that start with an
apostrope '

I need to remove the ' from the start of the function.

Tried using FIND/REPLACE but it does not like it.

Any ideas?
 
J

JLatham

The apostrophe creates a bit of a problem. But you can get around it.

Select the column and then COPY it.
Choose an empty column and use Paste Special and choose the Values option.
That will copy it all to the new column without the apostrophe. Then just
copy the new column back into the original one.

Quite often using the Paste Special will work without even changing columns,
but as I said, the apostrophe creates a bit of an issue, so the second column
is necessary in this case.
 
J

James T

Thank you..

Also found a really good macro to use:

Sub RemoveApostrophe()
' Removes hidden apostrophes as first character
' Works on formulas as well as text and values
For Each CurrentCell In Selection
If CurrentCell.HasFormula = False Then

'Checks to make sure that procedure does not
'change cell with a formula to be only the value
CurrentCell.Formula = CurrentCell.Value
End If
Next

End Sub
 
J

JLatham

I often use a formula similar to that one to "carve data in stone" to prevent
changes in calculated values. Figured the Copy/Paste Special would be a
little faster for you, although there are times it can fail. Glad this
wasn't one of them, or else we would have been forced to use the code you
found.

Enjoy.
 
Top