Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
FIND and REPLACE ' apostrophe
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
FIND and REPLACE ' apostrophe
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. "James T" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
FIND and REPLACE ' apostrophe
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
FIND and REPLACE ' apostrophe
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. "James T" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: Find and replace | Excel Discussion (Misc queries) | |||
How do I find and replace the " | Excel Discussion (Misc queries) | |||
Find and Replace | Excel Worksheet Functions | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
VB Find and Replace | Excel Worksheet Functions |