How to Insert Double or Single Quotes

Question

I have a long list of names that I need to have quotes around (it can be double or single quotes) and I have about 8,000 of them. I have them in Excel without any quotes and I can copy all of the names and paste them no problem but there are still no quotes. I have looked and looked for an Excel formula to add quotes to the name in each row but I have had no luck. I have also tried some clever find and replace techniques but no have worked either. The format I am looking for is this:

"Allen" or 'Allen'

Any of those would work. I need this so I can store the info into a database. Any help is greatly appreciated. Thanks

PS:

I have found other people online needing the same thing done that I need done and this solution has worked for them but I do not know what do with it:

You can fix it by using a range variable (myCell for example) and then use that to iterate the 'selection' collection of range objects, like so

Sub AddQuote()
Dim myCell As Range
    For Each myCell In Selection
        If myCell.Value <> "" Then
            myCell.Value = Chr(34) & myCell.Value
        End If
    Next myCell
End Sub

Another solution that also worked for others was:

Sub OneUglyExport()

Dim FileToSave, c As Range, OneBigOleString As String

FileToSave = Application.GetSaveAsFilename

Open FileToSave For Output As #1

For Each c In Selection

If Len(c.Text) <> 0 Then _

    OneBigOleString = OneBigOleString & ", " & Chr(34) & Trim(c.Text) & Chr(34)

Next

Print #1, Mid(OneBigOleString, 3, Len(OneBigOleString))

Close #1

End Sub

score 46 · Accepted Answer · edited Jun 20 '20 at 09:12

46

To Create New Quoted Values from Unquoted Values

Column A contains the names.
Put the following formula into Column B = """" & A1 & """"
Copy Column B and Paste Special -> Values

Using a Custom Function

Public Function Enquote(cell As Range, Optional quoteCharacter As String = """") As Variant
    Enquote = quoteCharacter & cell.value & quoteCharacter
End Function

=OfficePersonal.xls!Enquote(A1)

=OfficePersonal.xls!Enquote(A1, "'")

To get permanent quoted strings, you will have to copy formula values and paste-special-values.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 30 '10 at 02:21

AMissico

21,470
7
78
106

Hi, when I enter this formula = """" & A1 & """" into column B it only puts the string in A1 in quotes. This is great but is there a way to put A1-A8126 in quotes with one function? Thank for the help. I am not so good with Excel so thanks for your patience. – three3 Jun 30 '10 at 03:22
You can select the cell with the formula and drag it using the "grab-handle" in the lower-right corner of the cell's selection boarder. This will copy the formula. Or, you can just copy the cell and paste it, which will cause the formula to be updated with the correct cell references. **See the help topic *Move or copy a formula*** – AMissico Jun 30 '10 at 03:44
Wow that worked perfectly! But one more question and tip if you do not mind. I just realized I also need a comma at the end of the name. So for example: "Allen", "David", "Smith", and so on. I tried messing around with the formula you gave me but I could not get it to work. I really do appreciate the help so far. – three3 Jun 30 '10 at 04:08
If you want to just insert single quote in a cell you can simply do '' and it will work – Kremena Lalova Dec 17 '15 at 13:33
why we could not put " " " this based the normal excel logic. why have to " " " " 4 quote? – Michael Li Feb 11 '19 at 19:40
Instead of copying a formula down into multiple cells you can use an `ArrayFormula()` function. I can't be positive what it's called in Excel but in Google sheets if you had data in cells A2:A4 you could put in Cell B2 only. `=ArrayFormula(""""&A2:A4&"""")` and cells B2:B4 would fill in with the quoted strings. – Tod Sep 03 '20 at 00:23

score 27 · Answer 2 · answered Dec 28 '16 at 06:48

27

Easier steps:

Highlight the cells you want to add the quotes.
Go to Format–>Cells–>Custom
Copy/Paste the following into the Type field: \"@\" or \'@\'
Done!

answered Dec 28 '16 at 06:48

noobsee

806
15
29

score 24 · Answer 3 · answered Jun 30 '10 at 15:48

Assuming your data is in column A, add a formula to column B

="'" & A1 & "'"

and copy the formula down. If you now save to CSV, you should get the quoted values. If you need to keep it in Excel format, copy column B then paste value to get rid of the formula.

score 7 · Answer 4 · answered Sep 18 '12 at 16:28

7

Why not just use a custom format for the cell you need to quote?

If you set a custom format to the cell column, all values will take on that format.

For numbers....like a zip code....it would be this '#' For string text, it would be this '@'

You save the file as csv format, and it will have all the quotes wrapped around the cell data as needed.

answered Sep 18 '12 at 16:28

Kevin Mansel

2,351
1
16
15

This worked perfectly for me. Custom format my text (nvarchar) columns, save the file as CSV, open file in my editor, use some basic Macros to insert the necessary SQL bits, and my SQL insert statement block was ready to go. Thanks! – mjmoody383 Oct 09 '13 at 23:35
1

The perfect solution, if cells contain numbers use '#' otherwise '@' for text. – Muzafar Ali Jul 18 '17 at 06:03

score 7 · Answer 5 · answered Mar 27 '16 at 13:09

7

Or Select range and Format cells > Custom \"@\"

answered Mar 27 '16 at 13:09

spriteup

99
2
8

2

How does this not answer the question? Sure, as an answer it could maybe have contained more explanation but it's a perfectly valid solution. – Ciara Apr 20 '16 at 08:32
For reference, if you wanted to specify that number format using vba it would look like `Range(...).NumberFormat = "\""@\"""` – Marcucciboy2 Sep 20 '18 at 18:42

score 0 · Answer 6 · answered Jun 30 '10 at 01:57

0

If you save the Excel file as a CSV format file, you might find that the result is convenient to inserting into a database, though I'm not sure all of the fields would be quoted.

answered Jun 30 '10 at 01:57

Slartibartfast

1,694
9
8

I tried this but as you said the fields are not quoted unfortunately. – three3 Jun 30 '10 at 03:22

score 0 · Answer 7 · answered Mar 03 '23 at 09:33

I would like to summarize the methods, there are more than 4 methods:

Let A1 be your cell where you want to insert quotes.

1 . For Double Quotes:

=CHAR(34)&A1&CHAR(34)

For Single Quotes:

=CHAR(39)&A1&CHAR(39)

2 . =CONCATENATE("'",A1,"'")

3 . ="'"&A1&"'"

4 . Apply Custom Format.

Suppose you have a number and you have to insert quotes on that number:

Right click the cells:

Then click Format Cells

You will get this screen:

In the Type box write

'#'

Click 'OK' at the bottom of the screen.

You will get the result:

If you have text written in the cell then:

Click 'OK' at the bottom of the screen.

How to Insert Double or Single Quotes

7 Answers7

To Create New Quoted Values from Unquoted Values

Using a Custom Function

Linked