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

A

Arun

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.
 
D

Dave Peterson

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.)
 
R

Ron Coderre

Try this:

Select a column range containing the impacted cells (Like A1:A100)
Data>Text-to-Columns>Click the [Finish] button
Repeat for other columns.

Does that knock out all of the leading apostrophes?

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

XL2002, WinXP-Pro
 
P

patricklcarroll

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.
 
Top