Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
apostrophes
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. |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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 |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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 |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
apostrophes
"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. |
#6
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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 |
#7
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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 |
#8
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
"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. |
#9
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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-( |
#10
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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" 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 |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
apostrophes
Editclearformats 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). |
#12
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
"Dave Peterson" wrote in message ... 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. |
#13
Posted to microsoft.public.excel.misc,microsoft.public.excel
|
|||
|
|||
apostrophes
"Lori" wrote Editclearformats 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 |
#14
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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 wrote: <<snipped 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. -- Dave Peterson |
#15
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
"Dave Peterson" wrote 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. |
#16
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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. teepee wrote: "Dave Peterson" wrote 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 |
#17
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
"Dave Peterson" wrote in message ... 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. |
#18
Posted to microsoft.public.excel, microsoft.public.excel.misc
|
|||
|
|||
apostrophes
Phew !! that was hard-going.
Pete On Feb 13, 10:55*pm, "teepee" wrote: "Dave Peterson" wrote in message ... 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. |
#19
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
That was long way to go, but we got there <vbg!
teepee wrote: "Dave Peterson" wrote in message ... 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. -- Dave Peterson |
#20
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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. |
#21
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
"Dave Peterson" wrote in message ... That was long way to go, but we got there <vbg! If life was meant to be easy God wouldn't have sent dumb newbies ;=) |
#22
Posted to microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
apostrophes
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Apostrophes in Cell | Excel Worksheet Functions | |||
Apostrophes and Sorting | Excel Discussion (Misc queries) | |||
Removing errant apostrophes | Excel Worksheet Functions | |||
Problem using 'PROPER' with apostrophes | Excel Worksheet Functions | |||
how do i get rid of hidden apostrophes in cells? | Excel Worksheet Functions |