Removing leading single quote (') from cells with Search/Replace
 

I have put together a sheet full of formulas. When creating the formulas I
put a single quote in front of all the equal signs (ie. '=A1+A2) so that I
could see the formulas. My strategy was to do a Search/Replace to make the
conversion to formulas.

Search for: '=
Replace with: =

To my chagrin, the Search cannot find the single quote mark since it does
not show up in the cell. Please help!! My sheet has over 500 lines. I
can't go back through te cells one-by-one.

Removing leading single quote (') from cells with Search/Replace
 

One way is to use a macro:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection.Cells, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range containing some text values"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
If Left(.Value, 1) = "=" Then
.NumberFormat = "General"
.Value = .Value
End If
End With
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

I use $$$$$= to prefix my formulas sometimes.

But you can also enter the formulas as formulas and use
Tools|Options|view tab|check formulas

(ctrl-` control-backquote (to the left of the 1/! on my USA keyboard) also
toggles that formula view.)


Arun wrote:

I have put together a sheet full of formulas. When creating the formulas I
put a single quote in front of all the equal signs (ie. '=A1+A2) so that I
could see the formulas. My strategy was to do a Search/Replace to make the
conversion to formulas.

Search for: '=
Replace with: =

To my chagrin, the Search cannot find the single quote mark since it does
not show up in the cell. Please help!! My sheet has over 500 lines. I
can't go back through te cells one-by-one.

--

Dave Peterson

Removing leading single quote (') from cells with Search/Replace
 

Try this:

Select a column range containing the impacted cells (Like A1:A100)
DataText-to-ColumnsClick the [Finish] button
Repeat for other columns.

Does that knock out all of the leading apostrophes?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Arun" wrote:

I have put together a sheet full of formulas. When creating the formulas I
put a single quote in front of all the equal signs (ie. '=A1+A2) so that I
could see the formulas. My strategy was to do a Search/Replace to make the
conversion to formulas.

Search for: '=
Replace with: =

To my chagrin, the Search cannot find the single quote mark since it does
not show up in the cell. Please help!! My sheet has over 500 lines. I
can't go back through te cells one-by-one.

Removing leading single quote (') from cells with Search/Replace
 

Highlight the entire column.

Go to Data, text to columns, choose Delimited.
Check the Other Box and place the ' in the box.

Thant will take care of your problem.