apostrophes

[teepee]

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]

If they are like your example above, find and replace =' with = and that
should work on all sheets.

 

[Dave Peterson]

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.

 

[Dave Peterson]

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

 

[teepee]

If they are like your example above, find and replace =' with = and that
should work on all sheets.

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.

 

[teepee]

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

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

 

[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

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

 

[teepee]

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.

 

[teepee]

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

 

[Dave Peterson]

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.

 

[Lori]

Edit>clear>formats also removes the apostrophe prefix character if that
helps. If you want to retain formatting, make a copy of the worksheet so you
can paste formats from it after (note that pasting formats does not carry
over this setting).

 

[teepee]

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.

Doesn't seem to work then.

The object of the exercise is that I have a file called
www.xxx.com/1/results.csv which my sheet reads from.

If I close that file and open www.xxx.com/2/results.csv the links will
continue to hunt around for the original file.

But if I close the first file, copy in links like ='results.csv!$B1 and then
remove the apostrophes, they marry up to the new file automatically. I've
done this before so I know it works. But it's a bit of a dark art figuring
out how.

 

[teepee]

Edit>clear>formats also removes the apostrophe prefix character if that
helps.

Thanks. Not for me unfortuantely. I'll try in on 2003 instead of 2007 a bit
later

 

[Dave Peterson]

First, if you try to enter:
='results.csv!$B1
in a cell that's formatted as General (or anything but text), excel won't let
you. It won't pass excel syntax checks.

So I'm still at a loss over what you're trying to do and what you're trying to
fix.

 

[teepee]

First, if you try to enter:
='results.csv!$B1
in a cell that's formatted as General (or anything but text), excel won't
let
you. It won't pass excel syntax checks.

So I'm still at a loss over what you're trying to do and what you're
trying to
fix.

You can create that format in a text document and import it though. Or you
can write it in if you format the cell for text.

The purpose is to allow you to switch between outside source documents.

You have an online document called www.xxx.com/1/results.csv
You have refrences to that document eg =results.csv!$B1
If you close down the document and open www.xxx.com/2/results.csv Excel will
replace all the links in te document like:

=results.csv!$B1

with something like

http://www.xxx.com/1/[results.csv]results!B1

However if you have a link called ='results.csv!$B1 that won't be affected.

So when you open the new results.csv, if you copy ='results.csv!$B1 into the
cell location then remove the apostrophe it will point to the new file. Or
it should.

 

[Dave Peterson]

I'm still confused.

Do you want to change existing formulas to text so that when you open another
result.csv file, these formulas don't point at the old file?

If yes, then before you open the new results.csv,
select all the cells on the worksheet
Edit|replace
what: =
with: $$$$$=
replace all

Now there are no formulas in that worksheet. They're all text.

Open the other .csv file and move it into your workbook.

Then reverse the mass change:
Select all the cells
Edit|replace
what: $$$$$=
with: =
replace all

I still wouldn't use any apostrophes in these mass changes. There still a pain
to remove.

First, if you try to enter:
='results.csv!$B1
in a cell that's formatted as General (or anything but text), excel won't
let
you. It won't pass excel syntax checks.

So I'm still at a loss over what you're trying to do and what you're
trying to
fix.

You can create that format in a text document and import it though. Or you
can write it in if you format the cell for text.

The purpose is to allow you to switch between outside source documents.

You have an online document called www.xxx.com/1/results.csv
You have refrences to that document eg =results.csv!$B1
If you close down the document and open www.xxx.com/2/results.csv Excel will
replace all the links in te document like:

=results.csv!$B1

with something like

http://www.xxx.com/1/[results.csv]results!B1

However if you have a link called ='results.csv!$B1 that won't be affected.

So when you open the new results.csv, if you copy ='results.csv!$B1 into the
cell location then remove the apostrophe it will point to the new file. Or
it should.

 

[teepee]

I'm still confused.

Do you want to change existing formulas to text so that when you open
another
result.csv file, these formulas don't point at the old file?

If yes, then before you open the new results.csv,
select all the cells on the worksheet
Edit|replace
what: =
with: $$$$$=
replace all

Yes you're absolutely right - that's much easier. Many many thanks for the
help.

 

[Pete_UK]

Phew !! that was hard-going.

Pete

Yes you're absolutely right - that's much easier. Many many thanks for the
help.

 

[Dave Peterson]

That was long way to go, but we got there <vbg>!

Yes you're absolutely right - that's much easier. Many many thanks for the
help.

 

[Saruman]

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: