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
= """" & A1 & """"
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.