 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008apostrophes
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.  Signature-John Please rate when your question is answered to help us and others know what is helpful. > I have a formula with an apostrophe, but it's not a leading apostrophe. > [quoted text clipped - 8 lines] > 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. 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. > I have a formula with an apostrophe, but it's not a leading apostrophe. > [quoted text clipped - 8 lines] > circumstances. Does anyone know what those criteria are? On some > spreadsheets it works, on others it doesn't. SignatureDave Peterson And your formula should look more like: $$$$$='results.csv'!$b1 or $$$$$=results.csv!$b1 > A little experimenting will help you determine what works for you. > [quoted text clipped - 23 lines] > > Dave Peterson SignatureDave Peterson Sorry Dave I don't see how that will help? > And your formula should look more like: > $$$$$='results.csv'!$b1 [quoted text clipped - 33 lines] >> >> 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? > [quoted text clipped - 39 lines] > > > > Dave Peterson SignatureDave Peterson > 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. > > 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. SignatureDave Peterson 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). > 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 > You have to select the range to correct before you run the macro. > [quoted text clipped - 5 lines] > 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. 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. <<snipped>> > Doesn't seem to work then. > [quoted text clipped - 8 lines] > done this before so I know it works. But it's a bit of a dark art figuring > out how. SignatureDave Peterson > First, if you try to enter: > ='results.csv!$B1 [quoted text clipped - 5 lines] > 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. 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 [quoted text clipped - 27 lines] > cell location then remove the apostrophe it will point to the new file. Or > it should. SignatureDave Peterson > I'm still confused. > [quoted text clipped - 8 lines] > with: $$$$$= > replace all Yes you're absolutely right - that's much easier. Many many thanks for the help. Phew !! that was hard-going. Pete > > I'm still confused. > [quoted text clipped - 11 lines] > Yes you're absolutely right - that's much easier. Many many thanks for the > help. That was long way to go, but we got there <vbg>! > > I'm still confused. > > [quoted text clipped - 11 lines] > Yes you're absolutely right - that's much easier. Many many thanks for the > help. SignatureDave Peterson > 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 ;=) 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 --------------------------------------------------------------------------- > I have a formula with an apostrophe, but it's not a leading apostrophe. > [quoted text clipped - 8 lines] > 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. > Just happened across this post. > [quoted text clipped - 24 lines] > > circumstances. Does anyone know what those criteria are? On some > > spreadsheets it works, on others it doesn't. SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.