如何插入双引号或单引号

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:

我有一长串需要引号的名字(可以是双引号或单引号)，我有大约8000个。我在Excel中没有任何引号，我可以复制所有的名称并粘贴它们没问题，但仍然没有引号。我已经查找并寻找了一个Excel公式，在每一行的名称中添加引号，但我没有运气。我也尝试过一些聪明的查找和替换技术，但没有一个是有效的。我寻找的格式是:

"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:

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

您可以使用范围变量(例如myCell)来修复它，然后使用它来迭代范围对象的“选择”集合，就像这样

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

6 个解决方案

#1

To Create New Quoted Values from Unquoted Values

• Column A contains the names.
• 列A包含名称。
• Put the following formula into Column B = """" & A1 & """"
• 将下列公式代入B = """ & A1 """
• Copy Column B and Paste Special -> Values
• 复制列B并粘贴特殊的->值

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)

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

= OfficePersonal.xls !Enquote(A1,“”)

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

要获得永久引用的字符串，您将必须复制公式值和特定值。

#2

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

假设数据在A列，向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.

把公式抄下来。如果你现在保存到CSV，你应该得到所引用的值。如果需要保持Excel格式，复制B列，然后粘贴值以去掉公式。

#3

Easier steps:

简单的步骤:

1. Highlight the cells you want to add the quotes.
2. 高亮显示要添加引号的单元格。
3. Go to Format–>Cells–>Custom
4. 格式- >细胞- >自定义
5. Copy/Paste the following into the Type field: \"@\" or \'@\'
6. 复制/粘贴以下内容到类型字段:\“\”或“\”\“\”\“\”\“\”\
7. Done!
8. 完成了!

#4

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.

您将文件保存为csv格式，它将根据需要将所有的引号括在单元数据周围。

#5

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

或选择范围和格式单元格>自定义\“@\”

#6

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.

如果将Excel文件保存为CSV格式文件，您可能会发现将结果插入到数据库中很方便，尽管我不确定是否会引用所有字段。