vba 如何插入双引号或单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3145980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Insert Double or Single Quotes
提问by three3
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:
我有一长串需要用引号引起来的名字（可以是双引号或单引号），其中大约有 8,000 个。我在 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
回答by AMissico
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
- A 列包含名称。
- 将以下公式放入B列
= """" & A1 & """" - 复制 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.
要获得永久引用的字符串，您必须复制公式值并粘贴特殊值。
回答by dendarii
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 列然后粘贴值以摆脱公式。
回答by noobsee
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!
- 突出显示要添加引号的单元格。
- 转到格式–>单元格–>自定义
- 将以下内容复制/粘贴到类型字段中：\"@\"或\'@\'
- 完毕！
回答by Kevin Mansel
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 格式，它将根据需要将所有引号包裹在单元格数据周围。
回答by spriteup
Or Select range and Format cells > Custom \"@\"
或选择范围和格式单元格 > 自定义 \"@\"
回答by Slartibartfast
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 格式文件，您可能会发现将结果插入到数据库中很方便，但我不确定所有字段都会被引用。
