excelvbadouble-quotessingle-quotes# How to Insert Double or Single Quotes

### To Create New Quoted Values from Unquoted Values

### Using a Custom Function

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
```

Solution

- Column A contains the names.
- Put the following formula into Column B
`= """" & A1 & """"`

- Copy Column B and Paste Special -> Values

```
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.

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel