I have a formula with an apostrophe, but it's not a leading apostrophe.

='results.csv!$B1

I wrote it this way for reasons I won't bore you with, but there is a good
reason. The idea is that a particular moment I can take out the apostrophe
using find/replace and thus activate the formula.

I know removing the apostrophe by using find/replace doesn't work on leading
apostrophes, but it does work on non-leading apostrophes in some
circumstances. Does anyone know what those criteria are? On some
spreadsheets it works, on others it doesn't.

If they are like your example above, find and replace =' with = and that
should work on all sheets.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"teepee" wrote:


I have a formula with an apostrophe, but it's not a leading apostrophe.

='results.csv!$B1

I wrote it this way for reasons I won't bore you with, but there is a good
reason. The idea is that a particular moment I can take out the apostrophe
using find/replace and thus activate the formula.

I know removing the apostrophe by using find/replace doesn't work on leading
apostrophes, but it does work on non-leading apostrophes in some
circumstances. Does anyone know what those criteria are? On some
spreadsheets it works, on others it doesn't.

"John Bundy" (remove) wrote in message
...
If they are like your example above, find and replace =' with = and that
should work on all sheets.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


I tried that and it said Microsoft could not find any data to replace. But
as I say on other occasions it has worked. In fact I have another
spreadsheet open at the same time where it does work (although it doesn't
work if I copy the formula from the non-operative sheet into the operative
one or vice versa.) I just don't know what governs whether it works or not.

I also had it working on the non-operative spreadsheet at one stage, and
then it stopped when I made some changes. It's driving me nuts.

A little experimenting will help you determine what works for you.

But I wouldn't use an apostrophe to make a formula text. I'd use a different
character string:

$$$$$='results.csv!$b1

The leading apostrophe is difficult to change (as you've seen) and has some bad
side effects--a lingering
prefixcharacter problem.


teepee wrote:

I have a formula with an apostrophe, but it's not a leading apostrophe.

='results.csv!$B1

I wrote it this way for reasons I won't bore you with, but there is a good
reason. The idea is that a particular moment I can take out the apostrophe
using find/replace and thus activate the formula.

I know removing the apostrophe by using find/replace doesn't work on leading
apostrophes, but it does work on non-leading apostrophes in some
circumstances. Does anyone know what those criteria are? On some
spreadsheets it works, on others it doesn't.

--

Dave Peterson

And your formula should look more like:
$$$$$='results.csv'!$b1
or
$$$$$=results.csv!$b1





Dave Peterson wrote:

A little experimenting will help you determine what works for you.

But I wouldn't use an apostrophe to make a formula text. I'd use a different
character string:

$$$$$='results.csv!$b1

The leading apostrophe is difficult to change (as you've seen) and has some bad
side effects--a lingering
prefixcharacter problem.

teepee wrote:

I have a formula with an apostrophe, but it's not a leading apostrophe.

='results.csv!$B1

I wrote it this way for reasons I won't bore you with, but there is a good
reason. The idea is that a particular moment I can take out the apostrophe
using find/replace and thus activate the formula.

I know removing the apostrophe by using find/replace doesn't work on leading
apostrophes, but it does work on non-leading apostrophes in some
circumstances. Does anyone know what those criteria are? On some
spreadsheets it works, on others it doesn't.

--

Dave Peterson

--

Dave Peterson

Sorry Dave I don't see how that will help?

"Dave Peterson" wrote in message
...
And your formula should look more like:
$$$$$='results.csv'!$b1
or
$$$$$=results.csv!$b1





Dave Peterson wrote:

A little experimenting will help you determine what works for you.

But I wouldn't use an apostrophe to make a formula text. I'd use a
different
character string:

$$$$$='results.csv!$b1

The leading apostrophe is difficult to change (as you've seen) and has
some bad
side effects--a lingering
prefixcharacter problem.

teepee wrote:

I have a formula with an apostrophe, but it's not a leading apostrophe.

='results.csv!$B1

I wrote it this way for reasons I won't bore you with, but there is a
good
reason. The idea is that a particular moment I can take out the
apostrophe
using find/replace and thus activate the formula.

I know removing the apostrophe by using find/replace doesn't work on
leading
apostrophes, but it does work on non-leading apostrophes in some
circumstances. Does anyone know what those criteria are? On some
spreadsheets it works, on others it doesn't.

--

Dave Peterson

--

Dave Peterson

Experimenting won't help you determine when you can change them???

Using a different string won't help you avoid the problem?

I didn't see a question that actually asked how to remove those apostrophes--is
that what your real question was?

If that's what you meant, you can 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, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants in selection!"
Exit Sub
End If

For Each myCell In myRng.Cells
If myCell.PrefixCharacter = "'" Then
myCell.Value = myCell.Value
End If
Next myCell

End Sub

Select a range and try it out.

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

teepee wrote:

Sorry Dave I don't see how that will help?

"Dave Peterson" wrote in message
...
And your formula should look more like:
$$$$$='results.csv'!$b1
or
$$$$$=results.csv!$b1





Dave Peterson wrote:

A little experimenting will help you determine what works for you.

But I wouldn't use an apostrophe to make a formula text. I'd use a
different
character string:

$$$$$='results.csv!$b1

The leading apostrophe is difficult to change (as you've seen) and has
some bad
side effects--a lingering
prefixcharacter problem.

teepee wrote:

I have a formula with an apostrophe, but it's not a leading apostrophe.

='results.csv!$B1

I wrote it this way for reasons I won't bore you with, but there is a
good
reason. The idea is that a particular moment I can take out the
apostrophe
using find/replace and thus activate the formula.

I know removing the apostrophe by using find/replace doesn't work on
leading
apostrophes, but it does work on non-leading apostrophes in some
circumstances. Does anyone know what those criteria are? On some
spreadsheets it works, on others it doesn't.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

"Dave Peterson" wrote


Select a range and try it out.

It just says "No constants in selection"
Am I suppose to write the range into the VBA (the whole of column B and C in
this case) or just select them? The latter doesn't work I'm afraid.

Just happened across this post.

If you type a tilde character, (on the hash key next to the enter Key on my
keyboard), in a find and replace box with an apostrophe following it, you
can then remove a non leading apostrophe character.

Like this : Find What: ~'
Replace With:
--
Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Symantec Antivirus 10
---------------------------------------------------------------------------
"teepee" wrote in message
...

I have a formula with an apostrophe, but it's not a leading apostrophe.

='results.csv!$B1

I wrote it this way for reasons I won't bore you with, but there is a good
reason. The idea is that a particular moment I can take out the apostrophe
using find/replace and thus activate the formula.

I know removing the apostrophe by using find/replace doesn't work on
leading
apostrophes, but it does work on non-leading apostrophes in some
circumstances. Does anyone know what those criteria are? On some
spreadsheets it works, on others it doesn't.

You don't need the tilde for those non-leading apostrophes.

Saruman wrote:

Just happened across this post.

If you type a tilde character, (on the hash key next to the enter Key on my
keyboard), in a find and replace box with an apostrophe following it, you
can then remove a non leading apostrophe character.

Like this : Find What: ~'
Replace With:
--
Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Symantec Antivirus 10
---------------------------------------------------------------------------
"teepee" wrote in message
...

I have a formula with an apostrophe, but it's not a leading apostrophe.

='results.csv!$B1

I wrote it this way for reasons I won't bore you with, but there is a good
reason. The idea is that a particular moment I can take out the apostrophe
using find/replace and thus activate the formula.

I know removing the apostrophe by using find/replace doesn't work on
leading
apostrophes, but it does work on non-leading apostrophes in some
circumstances. Does anyone know what those criteria are? On some
spreadsheets it works, on others it doesn't.




--

Dave Peterson