Free Microsoft Excel 2013 Quick Reference

Excel inserts single quote marks into formula

When I use vba to insert this formula:
Sub Macro1()
ActiveCell.FormulaR1C1 = "=IF(F12=0," & Chr(34) & Chr(34) & Chr(34) &
Chr(34) & ",IF(F12<0.3,0.3-F12," & Chr(34) & Chr(34) & Chr(34) & Chr(34) &
"))"

End Sub
I get this result:
=IF('F12'=0,"""",IF('F12'<0.3,0.3-'F12',""""))

How do I stop Excel from inserting the single quotation marks?

Cheers,
Max


Post your answer or comment

comments powered by Disqus
Hi,

I am trying to insert a double quote mark into two places inside a formula in a cell in Excel 2000. I use the csv file output to create a PHP webpage. PHP side works, but having problem with either too many double qoute marks, or not enough, appearing in the csv file. I use the pipe symbol as a separator instead of a comma.

I have attached a sample workbook with excess details stripped out.

Brief summary of what the difference in the two examples are

1/ getting a double quote mark at beginning and end of field where there should be no quote mark

2/ getting two double quote marks in the middle of the field data where only one double quote mark should be

Example 1
current csv files output of cells G2 & H2:

|"http://www.seocom.com/postcard/ephemera/tmb_vtc64.jpg""><BR><img src=""http://www.seocom.com/postcard/ephemera/tmb_vtc64r.jpg"

|"http://www.seocom.com/postcard/ephemera/vtc64r.jpg""><B>View Rear Image</B></A><BR><A HREF=""http://www.seocom.com/postcard/ephemera/vtc64.jpg"|

Example 2
What I need for G2 & H2 to output in the csv file:

|http://www.seocom.com/postcard/ephemera/tmb_vtc64.jpg"><BR><img src="http://www.seocom.com/postcard/ephemera/tmb_vtc64r.jpg

|http://www.seocom.com/postcard/ephemera/vtc64r.jpg"><B>View Rear Image</B></A><BR><A HREF="http://www.seocom.com/postcard/ephemera/vtc64.jpg|

I have tried the &"""" solution as included in this workbook. Also have tried CHAR(34), &quot;, any number of double quote marks by themselves, and probably a few other solutions I have come across. without success. Like I said I seem to either end up with too many quote marks, or not enough...

Thanks,
Stan...

Hey, i'm an excel newbie.

My question is does anyone know if there is a formula to insert a check mark into a cell?

The user should be able to hit the spacebar or another key to insert the check mark. Anyone have any suggestions?

Thanks

Why does Access apend addl quote marks to records I cut/paste into say, notepad or excel? I don't seem them in access - but bang, there they are when I cut and paste. Each is supposed to start with a single quote mark (and then of course end with one) - but not two on each side. I've tried stripping them out via notepad, but that doesn't work, so apparently access is taking it upon itself to add one quote for each quote.

Yikes.

Ok seems simple enough but can't seem to find a solution. I have some cols of data which I'd like to copy but I want the values enclosed in double-quote marks i.e.
A1234 -> "A1234"
The columns of data will be updated continously so I need a formula that can put in the quotes. If I do something like ="B1" Excel thinks the quote marks are for formatting and I only get the B1 value without quotes.
After some processing the quoted cols are clipped to a text file for processing in another prgm -which requires quotes as a delimiter.
Any ideas on a formula for this?
Thanks
Ron.

Hi

All I winder if somebody could possibly help me, I have coded a micro which will out the value from a range of cells into a text file and a comma is used as a delimiter. The problem I am having is that excel is putting quote marks around each values from the cells, so out looks like this.

"233","3444","34443"

Can somedody please let me know how I can get rid of the quote marks, here is the code I am using.


	VB:
	
 
Sub WriteFile() 
     
    Dim oFSObj As Object, oTSStream As Object 
    Dim lngRow As Long, lngCol As Long 
    Dim rngeRangeToWrite As Range 
    Dim strTextFile As String, strDelimiter As String 
     
     
     'Choose your delimiter here.  Can be anything you want
    strDelimiter = "," 
     
     
     'You can either use the 'Used range', or use something like
    Set rngeRangeToWrite = Range("E2:G10") 
     'Set rngeRangeToWrite = ActiveSheet.UsedRange
     
     'Create a file system object and use it to create a new
     'text file.
    strTextFile = "C:myfile.txt" 
    Set oFSObj = CreateObject("Scripting.FilesystemObject") 
    Set oTSStream = oFSObj.CreateTextFile(strTextFile, True) 
     
     
     'This loop is for looping from the top of the sheet to the bottom
    For lngRow = 1 To rngeRangeToWrite.Rows.Count 
         
         'This loop will write one cell at a time for the current row
        For lngCol = 1 To rngeRangeToWrite.Columns.Count 
             
            oTSStream.Write Chr(34) & rngeRangeToWrite.Cells(lngRow, lngCol) & _ 
            Chr(34) & strDelimiter 
             
        Next lngCol 
         
         'The vbCr character denotes the end of a text file
        oTSStream.Write vbCr 
         
    Next lngRow 
     
    oTSStream.Close 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Many Thanks for taking the time to look at my post
cheers
Madmarkym

I have put together a sheet full of formulas. When creating the formulas I
put a single quote in front of all the equal signs (ie. '=A1+A2) so that I
could see the formulas. My strategy was to do a Search/Replace to make the
conversion to formulas.

Search for: '=
Replace with: =

To my chagrin, the Search cannot find the single quote mark since it does
not show up in the cell. Please help!! My sheet has over 500 lines. I
can't go back through te cells one-by-one.

Help! We have thousands of single quote marks to delete but when you find/replace Excel will remove the bold from the text within the quotes. An example is ' testing ' (I want to remove the ' and ' but keep the bolded word in the middle). I see the options listed and if I choose to replace with no quote and BOLD, it will make the whole cell bold. Can I do what I want to do with just removing the ' and ' or do I need to go through each cell to remove them?

Any help would be greatly appreciated.

how do I insert a "check mark" into a cell in Excel

I have set up an excel file that helps put together different text components using the vlookup and concatenate formulas for use in one plain text document .

When I try to paste the finished result into notepad/textpad/word it adds extra double quote marks throughout the text. Does anyone know how to paste it without these marks?

Thanks!

Hey, i'm an excel newbie.

My question is does anyone know if there is a formula to insert a check mark into a cell? The user should be able to hit the spacebar or another key to insert the check mark. Anyone have any suggestions?

Thanks

When I paste text that contains paragraph marks into Excel it breaks the text
at each CR into a different cell. How can I force Excel to copy a body of
text, including CRs, into a single cell.

TIA

Peter

Is there a way to insert a word document into a single cell of an excel
worksheet that would not expand the cell yet give you full veiw of the
document if you scrolled over the cell.
Let's say for example I am creating a schedule on a calender. I want to see
the heading of the word document when I open the excel workbook but want the
calender to remain the same size unless I scroll over the cell that holds the
information I need.

I'm having a small problem. I want to display a single quote as the first item in a cell. I know you can just type it twice to see it, but when you do this it looks fine in the cell, but the formula bar still shows both single quotes.

I read a cool tip of putting the text in the cell, proceeded by the double single quotes. then you put the number 1 in some other cell, copy the cell with the 1, do a paste special "multiply" on the cell with the double single quote.

This works beautifully! The cell shows only one single quote, the formula bar shows only one single quote... until... you go into the formula bar then hit enter. This causes Excel to reevaluate the cells contents, and will now drop the sinlge quote from the cell visibly.

so basically want i want is a cell and the formula bar to display: 'test data'

And I need to be able to go into the formula bar and back out and it not change the value from 'test data'

The bad part... I can't use macros as this is going to various clients who are so technically challenged they should even use computers in the first place. They can't undestand that it would be easier if their data had double quotes around it. The have no concept that certain characters have different meanings to Excel. And when they see the workaround of using the double single quote, they assmue that is an error and try to "fix" it. (a little frustrated by them if you can't tell)

Any advice?

Hi all...
I'm trying to assign a simple formula to the Activecell using VBA (I'm pretty new at VBA, by the way). The formula (in its final form) I'm trying to assign looks like:

In my VBA code, I'm substituting several variables in when trying to construct and assign the formula. My code looks like
this:

"right(currCell, holdlen)" contains the text string "16", and "nextCell" contains the test
string "17". So when I join it all together, I should get the final string "=Tickers!T16/Tickers!T17",
but instead it produces :

... with little single quotes around the T16 and T17. This causes a #NAME? error in the cell. Would anyone have any idea what
I'm doing wrong, and how to get those little single quotes out of there?

Thanks!
Shawn

Hoping someone can help me here because this makes no sense at all. I am trying to get Excel to auto insert single quotes around each cell in one of my columns. So I use this formula:

="'"&A2&"'"

Works great for cell A2. So I copy the formula and paste it into the next column. The formula shows up as

="'"&A3&"'"

But the displayed result is the contents of A2. If I paste the formula down the rest of my column, it copies A2's value all the way down even though the formula references different cells.

What am I missing here? Thanks!

I have an Excel 2003 workbook which I want to publish as an example of how to use a set of accompanying addin functions.
It has cells with comments which are not computed:
'This will convert from latitude/longitude to OS references:
'=osref(datum,latitude,longitude) and examples which are computed.
=osref(84,C1,D1) Unfortunately, the examples get changed to
='c:philApplication dataMicrosoftaddinsosgb.xla'!osref(84,C1,D1) when I uninstall the addin and/or migrate it to another system which destroys portability.

I've posted this problem with no response (thanks for looking anyway) so plan B was to write a function to strip out the offending junk (there maybe several in a single cell) and use it to re-write every cell with a formula in it. I then call this during the install process.

Unfortunately, when I read the cells I cannot distinguish between the comments and the real formulae. In its simplest form:

Sub test()
Dim myvariable As String
myvariable = ActiveSheet.Cells(1, 1).Formula
MsgBox myvariable
End Sub
This is stripping out the initial single quote from any comment cells.
Why is this and how can I identify these comment cells?

Generating this addin sounded so straightforward!

Regards
Phil

hi

I need some help here,
assuming value in cell S4 is 11 and value in cell S5 is 47, I've created this formula


the problem is, excel reads this formula like this


there is a double quotation mark (""), which is act like a string, so I can't get a summary from this formula. My question is, how to remove this quote mark so the excel read the formula as


Thanks for your help,
Regards,

Note : sorry I forgot to finish the subject title, it's my mistake

How can you get excel to find a single quote mark within a cell? The following formula does not work;


	VB:
	
=Find(A1,""") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to identify all cells with text strings that contain a single quotemark. I just want to make a list of all part numbers that have a single quotemark in their description (ie sheet, 2"x4").

Excel keeps telling me to use """" as my find text which doesn't work to find a single quotemark.

I have an inch marker (") in a text string that I want to do a COUNTIF formula on but Excel is trying to view that inch mark as a close quote mark. How do I get around this? The formula I tried is below:

=COUNTIF(S86:BF86,"1/2" HOLE")

As you can see this won't work because Excel wants me to put a begin quote before hole as well and never finds 1/2" HOLE because of it. Thanks for any help!

Hey guys,
I am trying to create a macro to insert a single quote (') in front of each zip code in a certain column. How do i do this? The macro should insert the character until it reaches the last row. New data is updated everyday so the macro would need to know when it reaches the last row. Thank you
EXAMPLE 91342 WOULD BE '91342

Hi
I need an help to create a macro to copy the value from number of excel sheets and paste it into another single excel sheet.
I have number files which are located in one location (ex: test, test1, test2, test3, test4)
I need to open each file and copy the item number which is in Cell B2 in the file named test.xls and paste it in the FINAL workbook and then again copy Total_Liab value in the tab named Calculations in the same workbook test.xls (total liability value) which is the last cell value in the column Q (example: Q51 in the Calculations sheet) Q51 and paste it next to the same item number in the FINAL workbook. Once it perform the action it should close the test.xls worksheet and then open the next test1.xls worksheet and should follow the same procedure as explained for the test.xls. Could you please help to create an macro

Hi,

I need some help with named ranges. I have the following equation in Sheet10:

=IF(ISERROR(VLOOKUP($A6+D$4,PO_1,1,0)),0,VLOOKUP($A6+D$4,PO_1,2,0))

PO_1 is a named range that does not exist, so normally it returns 0. On Sheet2, I have a Worksheet_Change event that fires when data is entered which causes the named range to be created:

ThisWorkbook.Names.Add Name:="PO_" & i, RefersTo:="='" & Sheet10.Range("H1") & "" & Sheet2.Range("E2") & "[" & Sheet2.Range("B" & 5 + i) & ".xls]Error Report" & "'!$A$16:$D$380"

The named range points to a range from another workbook, and a link to that file is created as a result.

However, if that other workbook is closed when the name is created, excel changes the equation in sheet10 to the following:

=IF(ISERROR(VLOOKUP($A6+D$4,'PO_1',1,0)),0,VLOOKUP($A6+D$4,'PO_1',2,0))

in effect, adding single quotes around the range name, and as a result the range isn't found, and the equation still returns 0. Unless I include a procedure to manually remove the single quotes, or I open the other workbook, it doesn't work.

Can anyone thing of a workaround? What am I doing wrong?

Thanks in advance,
Joseph Guindi

I have a table which has a column that uses a formula. When I insert a new
row in the middle of the table, Excel inserts a blank cell into the column
with formulas, rather than keeping the formula that applies to the adjacent
rows.

How do I insert a row and keep the column with a formula so it applies to
that new row?

Typed single quotation mark ' in cell, and it doesn't show up on sheet. I
remembered that I needed to type two single quotes to have one show up in
cell, but I don't remember why. Can someone refresh my memory please. Thank
you!


No luck finding an answer? You could always try Google.