In this post we will see how to insert excel formula in cells that have double quotes in them, there are number of instances where we are required to write formula(especially string related formula) with quotes(“”) and excel throws error, in this post we will see how to handle such cases.
In the example below we will see how to create an excel formula that has quotation marks embedded.
Step 1
Below is the sample data in which you can see column “Values” has some random data and we want to create a new column “New Values” in which we want to have the data from column “Values” but keep the cells as blank whenever we encounter “#NA”.
Step 2
We will write a formula with double quotes(“”) in VBA and will insert the formula in excel cells.
We will create a macro in excel to do this, open VB editor from Developer tab in excel as shown in the figure.
Insert a module in which we will write a small macro to insert the formula with double quote.
Now double click the newly created module to open that and paste the below code in the module.
Step 3
Sub Insert_Formula()
Worksheets(“Sheet1”).Range(“B2”).Formula = “=IF(Sheet1!A2=””#NA””,””””,Sheet1!A2)”
End Sub
This program will insert excel formula with double quotes in excel cell, now run it , Run the program by pressing F5 or by play button.
You can see that after the program is run, we have inserted the excel formula in cell B2 which serves our purpose.
Hope this helped.
