|
Unwanted single quote displayed in Formula bar
Cell A5 contains the text word TEST
Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? |
Unwanted single quote displayed in Formula bar
Tools, options, transition tab, transition navigation keys is apparently
turned on. This is placing a Lotus alignment code in the cell. ' (apostrophe) Left align data in the cell The alignment codes display only in the formula bar - not in the cell. "Joe" wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? |
Unwanted single quote displayed in Formula bar
What type of Paste/Special did you perform?
-- Gary''s Student - gsnu200717 "Joe" wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? |
Unwanted single quote displayed in Formula bar
I have checked the transition tab and "Transition navigation keys" are
unchecked. Under "Sheet options" Transition formula evaluation and "formula entry" are also unchecked. "BoniM" wrote: Tools, options, transition tab, transition navigation keys is apparently turned on. This is placing a Lotus alignment code in the cell. ' (apostrophe) Left align data in the cell The alignment codes display only in the formula bar - not in the cell. "Joe" wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? |
Unwanted single quote displayed in Formula bar
Paste Special " Values" and also tried "Values and number formats". Made no
difference. "Gary''s Student" wrote: What type of Paste/Special did you perform? -- Gary''s Student - gsnu200717 "Joe" wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? |
Unwanted single quote displayed in Formula bar
Saved from a previous post:
If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson |
Unwanted single quote displayed in Formula bar
Toggling the transition navigation key many times made no difference at all.
Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson |
Unwanted single quote displayed in Formula bar
That toggle allows you to see the apostrophe (or hide it). It doesn't remove it
at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
OK. When you do the last EDIT/Replace it does not change cell C5 since it
still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
I'd try it again. This has always worked for me.
Maybe you could test it in a test worksheet. You could also use a macro that looks at each cell in the selection: 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 len(mycell.value) = 0 then 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 Joe wrote: OK. When you do the last EDIT/Replace it does not change cell C5 since it still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
I noticed that I can get rid of the single quote (in cell C5) in the formula
bar by using the backspace key. So it seems that it really is present in the cell although it does not show up when not selected and it is not a character code 39. Next I tried your VBA macro and this one works and gets rid of the single quote over selected areas. However I have tried $$$$$ method) again and again. It still does not work for me. In the very first Edit/Replace where you use what: (leave blank) means that C5 won't be selected and replaced with $$$$$. Therefore it should not work at the next stage when all $$$$$ are selected and then replaced by blanks. "Dave Peterson" wrote: I'd try it again. This has always worked for me. Maybe you could test it in a test worksheet. You could also use a macro that looks at each cell in the selection: 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 len(mycell.value) = 0 then 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 Joe wrote: OK. When you do the last EDIT/Replace it does not change cell C5 since it still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
I've never seen the pair of edit|Replaces fail. I don't have a guess why it
fails for you. Joe wrote: I noticed that I can get rid of the single quote (in cell C5) in the formula bar by using the backspace key. So it seems that it really is present in the cell although it does not show up when not selected and it is not a character code 39. Next I tried your VBA macro and this one works and gets rid of the single quote over selected areas. However I have tried $$$$$ method) again and again. It still does not work for me. In the very first Edit/Replace where you use what: (leave blank) means that C5 won't be selected and replaced with $$$$$. Therefore it should not work at the next stage when all $$$$$ are selected and then replaced by blanks. "Dave Peterson" wrote: I'd try it again. This has always worked for me. Maybe you could test it in a test worksheet. You could also use a macro that looks at each cell in the selection: 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 len(mycell.value) = 0 then 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 Joe wrote: OK. When you do the last EDIT/Replace it does not change cell C5 since it still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
But I am not saying that it is failing.
It is just that the Edit/Replace refuses to select the cell C5 because it is not being told to do so by the first step. It (C5) really is not a blank cell but contains a single quote as indicated by the formula bar. "Dave Peterson" wrote: I've never seen the pair of edit|Replaces fail. I don't have a guess why it fails for you. Joe wrote: I noticed that I can get rid of the single quote (in cell C5) in the formula bar by using the backspace key. So it seems that it really is present in the cell although it does not show up when not selected and it is not a character code 39. Next I tried your VBA macro and this one works and gets rid of the single quote over selected areas. However I have tried $$$$$ method) again and again. It still does not work for me. In the very first Edit/Replace where you use what: (leave blank) means that C5 won't be selected and replaced with $$$$$. Therefore it should not work at the next stage when all $$$$$ are selected and then replaced by blanks. "Dave Peterson" wrote: I'd try it again. This has always worked for me. Maybe you could test it in a test worksheet. You could also use a macro that looks at each cell in the selection: 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 len(mycell.value) = 0 then 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 Joe wrote: OK. When you do the last EDIT/Replace it does not change cell C5 since it still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
If I type a single apstrophe in a bunch of cells and do the edit|Replace (leave
blank with $$$$$), those cells got changed. But the second edit|replace didn't fix the problem--I still saw the apostrophe in the cell. But if those cells were the result of formulas that evaluated to "", then this technique has never failed to clean those cells for me. Joe wrote: But I am not saying that it is failing. It is just that the Edit/Replace refuses to select the cell C5 because it is not being told to do so by the first step. It (C5) really is not a blank cell but contains a single quote as indicated by the formula bar. "Dave Peterson" wrote: I've never seen the pair of edit|Replaces fail. I don't have a guess why it fails for you. Joe wrote: I noticed that I can get rid of the single quote (in cell C5) in the formula bar by using the backspace key. So it seems that it really is present in the cell although it does not show up when not selected and it is not a character code 39. Next I tried your VBA macro and this one works and gets rid of the single quote over selected areas. However I have tried $$$$$ method) again and again. It still does not work for me. In the very first Edit/Replace where you use what: (leave blank) means that C5 won't be selected and replaced with $$$$$. Therefore it should not work at the next stage when all $$$$$ are selected and then replaced by blanks. "Dave Peterson" wrote: I'd try it again. This has always worked for me. Maybe you could test it in a test worksheet. You could also use a macro that looks at each cell in the selection: 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 len(mycell.value) = 0 then 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 Joe wrote: OK. When you do the last EDIT/Replace it does not change cell C5 since it still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
Just to keep kicking this dead horse...
Are you sure that those apostrophes were the results of a formula being converted to values? I ran this and found a difference between typing the apostrophe and converting to values: Option Explicit Sub testme() With Workbooks.Add(1).Worksheets(1).Range("a1") .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = "'" Debug.Print "after apostrophe: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Clear .Formula = "=""""" Debug.Print "with formula: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = .Value Debug.Print "after value = value: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) Debug.Print "----Typing in apostrophe manually--------------" .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = "'" .Replace what:="", replacement:="$$$$$" Debug.Print "after first replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Replace what:="$$$$$", replacement:="" Debug.Print "after second replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) Debug.Print "----using a formula--------------" .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Formula = "=""""" .Value = .Value .Replace what:="", replacement:="$$$$$" Debug.Print "after first replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Replace what:="$$$$$", replacement:="" Debug.Print "after second replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Parent.Parent.Close savechanges:=False End With End Sub And got these results: after clear: **-- = 0 after apostrophe: *'*-- = 1 with formula: **-- = 0 after value = value: **-- = 0 ----Typing in apostrophe manually-------------- after clear: **-- = 0 after first replace: *'*-- = 1 after second replace: *'*-- = 1 ----using a formula-------------- after clear: **-- = 0 after first replace: **-- = 0 after second replace: **-- = 0 Dave Peterson wrote: If I type a single apstrophe in a bunch of cells and do the edit|Replace (leave blank with $$$$$), those cells got changed. But the second edit|replace didn't fix the problem--I still saw the apostrophe in the cell. But if those cells were the result of formulas that evaluated to "", then this technique has never failed to clean those cells for me. Joe wrote: But I am not saying that it is failing. It is just that the Edit/Replace refuses to select the cell C5 because it is not being told to do so by the first step. It (C5) really is not a blank cell but contains a single quote as indicated by the formula bar. "Dave Peterson" wrote: I've never seen the pair of edit|Replaces fail. I don't have a guess why it fails for you. Joe wrote: I noticed that I can get rid of the single quote (in cell C5) in the formula bar by using the backspace key. So it seems that it really is present in the cell although it does not show up when not selected and it is not a character code 39. Next I tried your VBA macro and this one works and gets rid of the single quote over selected areas. However I have tried $$$$$ method) again and again. It still does not work for me. In the very first Edit/Replace where you use what: (leave blank) means that C5 won't be selected and replaced with $$$$$. Therefore it should not work at the next stage when all $$$$$ are selected and then replaced by blanks. "Dave Peterson" wrote: I'd try it again. This has always worked for me. Maybe you could test it in a test worksheet. You could also use a macro that looks at each cell in the selection: 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 len(mycell.value) = 0 then 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 Joe wrote: OK. When you do the last EDIT/Replace it does not change cell C5 since it still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
I think the "horse" is still alive and doing well.
To answer your question, yes those apostrophes were the results of a formula being converted to values? I have been doing some futher investigation. It is easy for me to reproduce this problem. I am using excel 2002 and start with a new blank Sheet. With Tools/Options/Transition/Transition navigation keys unchecked All cells are formatted under Number tab with General category. I type into cell A1 the text "test" into cell B1 the formula =IF(A1="x",A1,"") into cell C1 =ISBLANK(B1) and into D1 =CODE(B1) Now after pressing the enter key in B1 the cell appears" blank" but contains the result of the formula. Next selecting B1 right click COPY and without moving to another cell location right click again and do a Paste/Special/Values. With B1 still selected I get a blank looking cell B1 but with ' in the formula bar. Had I pasted/special/values in another location there would be no problem:the formula bar and cell would both be blank. Hope you can reproduce this. "Dave Peterson" wrote: Just to keep kicking this dead horse... Are you sure that those apostrophes were the results of a formula being converted to values? I ran this and found a difference between typing the apostrophe and converting to values: Option Explicit Sub testme() With Workbooks.Add(1).Worksheets(1).Range("a1") .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = "'" Debug.Print "after apostrophe: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Clear .Formula = "=""""" Debug.Print "with formula: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = .Value Debug.Print "after value = value: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) Debug.Print "----Typing in apostrophe manually--------------" .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = "'" .Replace what:="", replacement:="$$$$$" Debug.Print "after first replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Replace what:="$$$$$", replacement:="" Debug.Print "after second replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) Debug.Print "----using a formula--------------" .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Formula = "=""""" .Value = .Value .Replace what:="", replacement:="$$$$$" Debug.Print "after first replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Replace what:="$$$$$", replacement:="" Debug.Print "after second replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Parent.Parent.Close savechanges:=False End With End Sub And got these results: after clear: **-- = 0 after apostrophe: *'*-- = 1 with formula: **-- = 0 after value = value: **-- = 0 ----Typing in apostrophe manually-------------- after clear: **-- = 0 after first replace: *'*-- = 1 after second replace: *'*-- = 1 ----using a formula-------------- after clear: **-- = 0 after first replace: **-- = 0 after second replace: **-- = 0 Dave Peterson wrote: If I type a single apstrophe in a bunch of cells and do the edit|Replace (leave blank with $$$$$), those cells got changed. But the second edit|replace didn't fix the problem--I still saw the apostrophe in the cell. But if those cells were the result of formulas that evaluated to "", then this technique has never failed to clean those cells for me. Joe wrote: But I am not saying that it is failing. It is just that the Edit/Replace refuses to select the cell C5 because it is not being told to do so by the first step. It (C5) really is not a blank cell but contains a single quote as indicated by the formula bar. "Dave Peterson" wrote: I've never seen the pair of edit|Replaces fail. I don't have a guess why it fails for you. Joe wrote: I noticed that I can get rid of the single quote (in cell C5) in the formula bar by using the backspace key. So it seems that it really is present in the cell although it does not show up when not selected and it is not a character code 39. Next I tried your VBA macro and this one works and gets rid of the single quote over selected areas. However I have tried $$$$$ method) again and again. It still does not work for me. In the very first Edit/Replace where you use what: (leave blank) means that C5 won't be selected and replaced with $$$$$. Therefore it should not work at the next stage when all $$$$$ are selected and then replaced by blanks. "Dave Peterson" wrote: I'd try it again. This has always worked for me. Maybe you could test it in a test worksheet. You could also use a macro that looks at each cell in the selection: 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 len(mycell.value) = 0 then 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 Joe wrote: OK. When you do the last EDIT/Replace it does not change cell C5 since it still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
I use xl2003, but it worked the same way in xl2002 for me.
I put the info in A1:d1. (and all the options in tools|Options|transitions tab unchecked) A1 showed: text b1 looked empty c1 showed: false d1 showed: #value! Is that what you got? Then I converted B1 to values. The formula bar looked empty with B1 selected A1:B1 still displayed the same results. Do you have any other options checked in tools|Options|transtions tab checked? And you did this test on a brand new worksheet in a brand new workbook? Joe wrote: I think the "horse" is still alive and doing well. To answer your question, yes those apostrophes were the results of a formula being converted to values? I have been doing some futher investigation. It is easy for me to reproduce this problem. I am using excel 2002 and start with a new blank Sheet. With Tools/Options/Transition/Transition navigation keys unchecked All cells are formatted under Number tab with General category. I type into cell A1 the text "test" into cell B1 the formula =IF(A1="x",A1,"") into cell C1 =ISBLANK(B1) and into D1 =CODE(B1) Now after pressing the enter key in B1 the cell appears" blank" but contains the result of the formula. Next selecting B1 right click COPY and without moving to another cell location right click again and do a Paste/Special/Values. With B1 still selected I get a blank looking cell B1 but with ' in the formula bar. Had I pasted/special/values in another location there would be no problem:the formula bar and cell would both be blank. Hope you can reproduce this. "Dave Peterson" wrote: Just to keep kicking this dead horse... Are you sure that those apostrophes were the results of a formula being converted to values? I ran this and found a difference between typing the apostrophe and converting to values: Option Explicit Sub testme() With Workbooks.Add(1).Worksheets(1).Range("a1") .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = "'" Debug.Print "after apostrophe: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Clear .Formula = "=""""" Debug.Print "with formula: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = .Value Debug.Print "after value = value: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) Debug.Print "----Typing in apostrophe manually--------------" .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = "'" .Replace what:="", replacement:="$$$$$" Debug.Print "after first replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Replace what:="$$$$$", replacement:="" Debug.Print "after second replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) Debug.Print "----using a formula--------------" .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Formula = "=""""" .Value = .Value .Replace what:="", replacement:="$$$$$" Debug.Print "after first replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Replace what:="$$$$$", replacement:="" Debug.Print "after second replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Parent.Parent.Close savechanges:=False End With End Sub And got these results: after clear: **-- = 0 after apostrophe: *'*-- = 1 with formula: **-- = 0 after value = value: **-- = 0 ----Typing in apostrophe manually-------------- after clear: **-- = 0 after first replace: *'*-- = 1 after second replace: *'*-- = 1 ----using a formula-------------- after clear: **-- = 0 after first replace: **-- = 0 after second replace: **-- = 0 Dave Peterson wrote: If I type a single apstrophe in a bunch of cells and do the edit|Replace (leave blank with $$$$$), those cells got changed. But the second edit|replace didn't fix the problem--I still saw the apostrophe in the cell. But if those cells were the result of formulas that evaluated to "", then this technique has never failed to clean those cells for me. Joe wrote: But I am not saying that it is failing. It is just that the Edit/Replace refuses to select the cell C5 because it is not being told to do so by the first step. It (C5) really is not a blank cell but contains a single quote as indicated by the formula bar. "Dave Peterson" wrote: I've never seen the pair of edit|Replaces fail. I don't have a guess why it fails for you. Joe wrote: I noticed that I can get rid of the single quote (in cell C5) in the formula bar by using the backspace key. So it seems that it really is present in the cell although it does not show up when not selected and it is not a character code 39. Next I tried your VBA macro and this one works and gets rid of the single quote over selected areas. However I have tried $$$$$ method) again and again. It still does not work for me. In the very first Edit/Replace where you use what: (leave blank) means that C5 won't be selected and replaced with $$$$$. Therefore it should not work at the next stage when all $$$$$ are selected and then replaced by blanks. "Dave Peterson" wrote: I'd try it again. This has always worked for me. Maybe you could test it in a test worksheet. You could also use a macro that looks at each cell in the selection: 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 len(mycell.value) = 0 then 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 Joe wrote: OK. When you do the last EDIT/Replace it does not change cell C5 since it still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Unwanted single quote displayed in Formula bar
To answer your questions.
"Is that what you got?" Yes. "Do you have any other options checked in tools|Options|transtions tab checked?" No, except "Microsoft Excel menus radio button" is the only thing selected. "And you did this test on a brand new worksheet in a brand new workbook?" No. Only on the same sheet. I next tried it on a new workbook, new sheet and behold, like your results, there was no problem! So I retraced my steps. What I did on a new workbook/ sheet was to write the formula in another cell say E1 for display purposes. E1 cell had in it '=IF(A1="x",A1,"") as text. I copied E1 and pasted it into B1 and then removed the ' It is then, if you go through the procedure (Copy/PasteSpecial/Values) that the problem arises in the formula bar. From then on, after deleting the contents of B1, and actually typing in the same formula into B1 and following the same procedure produces the single quote probem on that sheet. It now becomes the offending sheet. I hope you can now reproduce this problem. If not, I can always send you the offending workbook. "Dave Peterson" wrote: I use xl2003, but it worked the same way in xl2002 for me. I put the info in A1:d1. (and all the options in tools|Options|transitions tab unchecked) A1 showed: text b1 looked empty c1 showed: false d1 showed: #value! Is that what you got? Then I converted B1 to values. The formula bar looked empty with B1 selected A1:B1 still displayed the same results. Do you have any other options checked in tools|Options|transtions tab checked? And you did this test on a brand new worksheet in a brand new workbook? Joe wrote: I think the "horse" is still alive and doing well. To answer your question, yes those apostrophes were the results of a formula being converted to values? I have been doing some futher investigation. It is easy for me to reproduce this problem. I am using excel 2002 and start with a new blank Sheet. With Tools/Options/Transition/Transition navigation keys unchecked All cells are formatted under Number tab with General category. I type into cell A1 the text "test" into cell B1 the formula =IF(A1="x",A1,"") into cell C1 =ISBLANK(B1) and into D1 =CODE(B1) Now after pressing the enter key in B1 the cell appears" blank" but contains the result of the formula. Next selecting B1 right click COPY and without moving to another cell location right click again and do a Paste/Special/Values. With B1 still selected I get a blank looking cell B1 but with ' in the formula bar. Had I pasted/special/values in another location there would be no problem:the formula bar and cell would both be blank. Hope you can reproduce this. "Dave Peterson" wrote: Just to keep kicking this dead horse... Are you sure that those apostrophes were the results of a formula being converted to values? I ran this and found a difference between typing the apostrophe and converting to values: Option Explicit Sub testme() With Workbooks.Add(1).Worksheets(1).Range("a1") .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = "'" Debug.Print "after apostrophe: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Clear .Formula = "=""""" Debug.Print "with formula: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = .Value Debug.Print "after value = value: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) Debug.Print "----Typing in apostrophe manually--------------" .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Value = "'" .Replace what:="", replacement:="$$$$$" Debug.Print "after first replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Replace what:="$$$$$", replacement:="" Debug.Print "after second replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) Debug.Print "----using a formula--------------" .Clear Debug.Print "after clear: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Formula = "=""""" .Value = .Value .Replace what:="", replacement:="$$$$$" Debug.Print "after first replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Replace what:="$$$$$", replacement:="" Debug.Print "after second replace: *" _ & .PrefixCharacter & "*-- = " & Len(.PrefixCharacter) .Parent.Parent.Close savechanges:=False End With End Sub And got these results: after clear: **-- = 0 after apostrophe: *'*-- = 1 with formula: **-- = 0 after value = value: **-- = 0 ----Typing in apostrophe manually-------------- after clear: **-- = 0 after first replace: *'*-- = 1 after second replace: *'*-- = 1 ----using a formula-------------- after clear: **-- = 0 after first replace: **-- = 0 after second replace: **-- = 0 Dave Peterson wrote: If I type a single apstrophe in a bunch of cells and do the edit|Replace (leave blank with $$$$$), those cells got changed. But the second edit|replace didn't fix the problem--I still saw the apostrophe in the cell. But if those cells were the result of formulas that evaluated to "", then this technique has never failed to clean those cells for me. Joe wrote: But I am not saying that it is failing. It is just that the Edit/Replace refuses to select the cell C5 because it is not being told to do so by the first step. It (C5) really is not a blank cell but contains a single quote as indicated by the formula bar. "Dave Peterson" wrote: I've never seen the pair of edit|Replaces fail. I don't have a guess why it fails for you. Joe wrote: I noticed that I can get rid of the single quote (in cell C5) in the formula bar by using the backspace key. So it seems that it really is present in the cell although it does not show up when not selected and it is not a character code 39. Next I tried your VBA macro and this one works and gets rid of the single quote over selected areas. However I have tried $$$$$ method) again and again. It still does not work for me. In the very first Edit/Replace where you use what: (leave blank) means that C5 won't be selected and replaced with $$$$$. Therefore it should not work at the next stage when all $$$$$ are selected and then replaced by blanks. "Dave Peterson" wrote: I'd try it again. This has always worked for me. Maybe you could test it in a test worksheet. You could also use a macro that looks at each cell in the selection: 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 len(mycell.value) = 0 then 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 Joe wrote: OK. When you do the last EDIT/Replace it does not change cell C5 since it still contains '$$$$$ The apostrophe seems to be a real character here not just a ghost on the toolbar. So it does not replace it. "Dave Peterson" wrote: That toggle allows you to see the apostrophe (or hide it). It doesn't remove it at all. And you didn't read the next portion: Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Joe wrote: Toggling the transition navigation key many times made no difference at all. Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$ I suppose using NA() instead of "" circumvents the problem and is a solution but is not an explanation of what else could be happening. "Dave Peterson" wrote: Saved from a previous post: If you want to see what's left in that cell after you convert ="" to values, try: Tools|Options|Transition Tab|Toggle Transition Navigation keys on. Then select one of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. ================= If you know that you're going to convert to values when you're done, you could modify your formulas to make it slightly easier: =IF(MID(A5,LEN(A5),1)="q",A5&"ue",na()) or even =IF(right(A5,1)="q",A5&"ue",na()) Then just change #n/a to nothing after you convert to values. Joe wrote: Cell A5 contains the text word TEST Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","") The result is a "blank" or a "" type of cell content since the result is FALSE. Now if I select this cell and copy it and then PASTE/Special into the same C5 position I get a single quote ' to appear on the formula bar but not in the cell when C5 is selected. Placing the formula = code(C5) into cell D5 gives #VALUE! Does anyone have an explanation as to what might be going on here? -- Dave Peterson -- |
Unwanted single quote displayed in Formula bar
It still worked fine for me.
If you want to send me a private email, just remove the xspam. Joe wrote: To answer your questions. "Is that what you got?" Yes. "Do you have any other options checked in tools|Options|transtions tab checked?" No, except "Microsoft Excel menus radio button" is the only thing selected. "And you did this test on a brand new worksheet in a brand new workbook?" No. Only on the same sheet. I next tried it on a new workbook, new sheet and behold, like your results, there was no problem! So I retraced my steps. What I did on a new workbook/ sheet was to write the formula in another cell say E1 for display purposes. E1 cell had in it '=IF(A1="x",A1,"") as text. I copied E1 and pasted it into B1 and then removed the ' It is then, if you go through the procedure (Copy/PasteSpecial/Values) that the problem arises in the formula bar. From then on, after deleting the contents of B1, and actually typing in the same formula into B1 and following the same procedure produces the single quote probem on that sheet. It now becomes the offending sheet. I hope you can now reproduce this problem. If not, I can always send you the offending workbook. <<snipped |
Unwanted single quote displayed in Formula bar
|
Unwanted single quote displayed in Formula bar
Sent in a private email:
I think it's that pesky ' that you use to make the formula text. If you run a macro like this: option explicit sub testme01() msgbox range("b5").prefixcharacter end sub after you do each step, you'll see that excel will see a prefixcharacter in that cell. I pasted from E5 to B5 and ran that code and saw: ' (the apostrophe) then I removed the apostrophe and saw: (nothing) (the apostrophe was gone) But when I converted to values and ran that code, the apostrophe was back. I've been lucky in that I don't use the apostrophe to force my formulas to text. I use $$$$$. $$$$$=IF(MID(A5,LEN(A5),1)="q",A5&"ue","") (or some other non-apostrophe string) Dave Peterson wrote: It still worked fine for me. If you want to send me a private email, just remove the xspam. Joe wrote: To answer your questions. "Is that what you got?" Yes. "Do you have any other options checked in tools|Options|transtions tab checked?" No, except "Microsoft Excel menus radio button" is the only thing selected. "And you did this test on a brand new worksheet in a brand new workbook?" No. Only on the same sheet. I next tried it on a new workbook, new sheet and behold, like your results, there was no problem! So I retraced my steps. What I did on a new workbook/ sheet was to write the formula in another cell say E1 for display purposes. E1 cell had in it '=IF(A1="x",A1,"") as text. I copied E1 and pasted it into B1 and then removed the ' It is then, if you go through the procedure (Copy/PasteSpecial/Values) that the problem arises in the formula bar. From then on, after deleting the contents of B1, and actually typing in the same formula into B1 and following the same procedure produces the single quote probem on that sheet. It now becomes the offending sheet. I hope you can now reproduce this problem. If not, I can always send you the offending workbook. <<snipped -- Dave Peterson |
| All times are GMT +1. The time now is 11:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com