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

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

"John Bundy" <(E-Mail Removed)(remove)> wrote in message
news:30000838-1797-4D9F-8233-(E-Mail Removed)...
> 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.

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

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> 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.

Curiously if I go into the formula bar, move the apostrophe anywhere else in
the formula (and this remains true even if I then move it back again
afterwards) and press return, I can find/replace the apostrophe.

Of course I would have to do this on each cell individually which will take
a very long time 8-(

You have to select the range to correct before you run the macro.

And the macro removes the apostrophe at the front of the cell that forces excel
to treat the entry as text.

I guess I don't understand what values you're starting with and what you want
them to look like when you're done.

teepee wrote:
>
> "Dave Peterson" <(E-Mail Removed)> 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.

--

Dave Peterson