vba 如果包含特定值，则将值从一列复制到另一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17915155/
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
Copying value from one column to another if it includes certain value
提问by user2628699
Original Image .. http://screencast.com/t/jk3IIbuuV
原始图像.. http://screencast.com/t/jk3IIbuuV
Need to grab 5 types of data from a row of ~500 and paste into 5 separate rows. Ideally the data would be transformed as shown to the right of the screenshot.
需要从约 500 行中抓取 5 种类型的数据并粘贴到 5 个单独的行中。理想情况下，数据将如屏幕截图右侧所示进行转换。
- Name of clinic
- Clinic email address
- Website
- Name of representative
- Type of clinic
- 诊所名称
- 诊所电子邮件地址
- 网站
- 代表姓名
- 诊所类型
I'm thinking some type of If formula/macro which searches for capitals, another which searches for @ and returns the string, another which searches for www, and another which searches for a bullet point. Possible?
我正在考虑某种类型的 If 公式/宏，它搜索大写，另一个搜索 @ 并返回字符串，另一个搜索 www，另一个搜索项目符号。可能的？
回答by Andre Silva
Ok, you just gave a little bit rows of your 500 hundreds so I assumed some patterns:
好吧，你只是给出了 500 行的一点点，所以我假设了一些模式：
I think I got the "Name of Clinic", "Website" and "E-mail",
我想我得到了“诊所名称”、“网站”和“电子邮件”，
Data is on column A.
Examples will be shown for one cell, but formulas are reproducible for other cells (just drag them).
数据在列上A。将显示一个单元格的示例，但公式可用于其他单元格（只需拖动它们）。
Name of clinic.
诊所名称。
Convert all strings in upper case strings.
将所有字符串转换为大写字符串。
B1 =UPPER(A1)
Check if the new string (B1) is different from the original one (A1).
检查新字符串 (B1) 是否与原始字符串 (A1) 不同。
C1 =IF(EXACT(A1,B1),A1,"")
Website
网站
Track character position of: "www."
跟踪字符位置：“www”。
B1 =SEARCH("www.",A1,1)
Crop string after "www."
在“www”之后裁剪字符串。
C1 =MID(A1,B1,100)
Track character position of: ".cl" (assuming all examples you gave had websites finishing only with ".cl").
跟踪字符位置：“.cl” （假设您提供的所有示例都有仅以“.cl”结尾的网站）。
D1 =SEARCH(".cl",C1,1)
Replace everything at right of ".cl" (just in case the string does not finish in ".cl"), for ".cl" only.
仅替换“.cl”右侧的所有内容（以防字符串未以“.cl”结尾），仅用于“.cl”。
E1 =REPLACE(C1,D1,100,".cl")
电子邮件
The same idea as website, but now, making stronger assumptions about the raw data:
- all phone numbers have the format (2) XXXX XXXX or (2) XXXX-XXXX;
- the e-mail information is right away on the right of the phone number.
与网站的想法相同，但现在对原始数据做出更强的假设：
- 所有电话号码的格式为 (2) XXXX XXXX 或 (2) XXXX-XXXX；
- 电子邮件信息就在电话号码的右边。
B1 =FIND("(2)",A1,1)
C1 =MID(A1,B1,100)
D1 =MID(K18,14,100) #remove the phone number (13 characters)#
E1 =SEARCH(".cl",L18,1)
F1 =REPLACE(D1,E1,100,".cl")
I did not try to gather the "Type of Clinic", because I could not reproduce the bullet on Excel (actually, I copied and pasted bullets from MS Word, but I am not sure if it was the same data format as yours). My first attempt would be to use the formula B1 =left(A1,1), to extract the first character of each string. Probably there will be a pattern (same character) for all text/strings with bullet. Then, one would just filter the cells selecting the bullet character and copy/past "Type of Clinic" information.
我没有尝试收集“诊所类型”，因为我无法在 Excel 上重现项目符号（实际上，我从 MS Word 复制并粘贴了项目符号，但我不确定它是否与您的数据格式相同）。我的第一次尝试是使用公式B1 =left(A1,1)，提取每个字符串的第一个字符。可能所有带有项目符号的文本/字符串都会有一个模式（相同的字符）。然后，只需过滤选择项目符号字符的单元格并复制/过去“诊所类型”信息。
I have no idea about how to retrieve the "Contact Name", but with the suggestions I proposed here it would be possible to eliminate the rows with information of "Type of Clinic" and "Name of Clinic".
我不知道如何检索“联系人姓名”，但是根据我在这里提出的建议，可以消除包含“诊所类型”和“诊所名称”信息的行。
