Deleting apostrophe before equals sign using search and replace

  • Thread starter Thread starter Trevor
  • Start date Start date
T

Trevor

I placed an apostrophe before the equal sign to turn a
formula into a label.
I tried used search and replace to delete the apostrophe
but the apostrophe is not recognised.
Any ideas? I have a spreadheet full of these
labels/formulae.
 
Trevor said:
I placed an apostrophe before the equal sign to turn a
formula into a label.
I tried used search and replace to delete the apostrophe
but the apostrophe is not recognised.
Any ideas? I have a spreadheet full of these
labels/formulae.
Click to expand...

Use tilde sign (~) before apostrophe.
 
Unlucky choice of characters!

One way to correct this is to use a macro:

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myRng As Range

Set myRng = Selection
For Each myCell In myRng.Cells
If myCell.PrefixCharacter = "'" Then
myCell.Formula = myCell.Value
End If
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 change = to $$$$$= when I want to convert formulas to text.)
 
Brilliant Dave

Worked a dream

Thanks
-----Original Message-----
Unlucky choice of characters!

One way to correct this is to use a macro:

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myRng As Range

Set myRng = Selection
For Each myCell In myRng.Cells
If myCell.PrefixCharacter = "'" Then
myCell.Formula = myCell.Value
End If
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 change = to $$$$$= when I want to convert formulas to text.)


--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Trevor said:
Thanks Bob

I'll use a tilde instead of apostrophe next time.

Bearing in mind I have a spreadsheet full of these, I did
a search and replace and it places the tilde after the
apostrophe. SO the "invisible" apostrophe is still there.
Click to expand...


Sorry Trevor, although you clearly said "apostrophe", I took it as
"quotation mark".

An alternative to the programming solution is the following (works in Excel
2002, don't know for other versions):

1. In an empty cell enter 1, then Copy that cell.
2. Select the range with apostrophes before =.
3. From the menu, Edit-->Paste Special-->Multiply.
4. Again from the menu Edit-->Replace-->In Find What, enter =, and in
Replace With also = (silly, isn't it?). Hit Replace All.

That should do it.
 
Back
Top