vba Excel：从单元格中提取由标记分隔的文本字符串的公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27803560/
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
Excel: Formula to extract a string of text delimited by markers from cells
提问by Nox
I'm messing with a spreadsheet containing postal addresses that have been inserted in the cells' comments Each comment contain an address composed of a variable number of lines (damn UK addresses, they can have up to 7 lines!) in the following format:
我正在处理一个包含已插入单元格注释中的邮政地址的电子表格每个注释都包含一个由可变行数（该死的英国地址，它们最多可以有 7 行！）组成的地址，格式如下：
Line1,
Line2,
Line3,
[...],
State
With my poor skills, I've managed to extract the comment with a VBA script, obtaining the following string on a single cell:
由于我的技能很差，我设法用 VBA 脚本提取了评论，在单个单元格上获得了以下字符串：
Line1,Line2,Line3,[...],State
At this point each string between commas must be extracted to its own cell. I've managed to extract the 1st 3 lines with the following formulas:
此时，逗号之间的每个字符串都必须提取到其自己的单元格中。我设法使用以下公式提取了第一 3 行：
For Line1:
对于线路 1：
=LEFT(A8;(SEARCH(",";A8))-1)
For Line2:
对于 Line2：
=MID(A8; SEARCH(",";A8)+1; SEARCH(","; A8; SEARCH(","; A8)+1)-SEARCH(",";A8)-1)
For Line3:
对于 Line3：
=MID(A8; SEARCH(",";A8;SEARCH(",";A8;SEARCH(",";A8;SEARCH(",";A8)))+1)+1;SEARCH(","; A8; SEARCH(","; A8;SEARCH(",";A8)+1)+1)-SEARCH(",";A8;SEARCH(",";A8)+1)-1)
From this point I start to get overflow errors from my brain... I probably need some days of sleep.
从这一点开始，我的大脑开始出现溢出错误……我可能需要几天的睡眠。
Can anybody help me to get to "line6", and finally suggest me how to pull out the "State line" which ends without comma?
谁能帮我转到“第6行”，最后建议我如何拉出没有逗号结尾的“州行”？
I thought I could pull out the "State" line with =RIGHT(",";SEARCH(",";A8)-1) but I'm obviously doing something wrong because that pulls out a comma instead of a string.
我以为我可以用 =RIGHT(",";SEARCH(",";A8)-1) 拉出“State”行，但我显然做错了，因为这会拉出逗号而不是字符串。
I guess I could do everything with a VBA script, but I'm not that skilled yet :(
我想我可以用 VBA 脚本做所有事情，但我还没有那么熟练:(
采纳答案by KevenDenen
If you are wanting to do this programmatically instead of using a built-in, check out the split function for chopping up your comma separated string. It will split up your input string into an array. Then you can do whatever you like with the array.
如果您想以编程方式执行此操作而不是使用内置函数，请查看 split 函数以切碎逗号分隔的字符串。它会将您的输入字符串拆分为一个数组。然后你可以对数组做任何你喜欢的事情。
Dim Names() As String
Names() = Split(inputValue, ",")
For i = 0 To UBound(Names)
' do what you want with each piece
Next
Gary's Student's answer is great for using the built-in functions.
Gary's Student 的答案非常适合使用内置函数。
回答by Gary's Student
With comma separated data in A1,in B1enter:
在A1 中使用逗号分隔数据，在B1 中输入：
=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
and copy across. For example:
并复制过去。例如：
Note:
笔记：
Why not use TextToColumns?
为什么不使用TextToColumns？
- The row of formulas re-calculates automatically if A1changes.
- The row of formulas will work even if A1, itself, contains a formula.
- 如果A1更改，公式行会自动重新计算。
- 即使A1本身包含公式，该行公式也将起作用。
回答by silentsurfer
If you want a VBA solution:
如果你想要一个 VBA 解决方案：
Sub spitString()
Dim sourceRange As Range
Dim stringArr() As String
Dim i As Integer
Set sourceRange = ActiveSheet.Range("A1")
stringArr = Split(sourceRange.Value, ",")
For i = LBound(stringArr) To UBound(stringArr)
sourceRange.Offset(0, i + 1).Value = stringArr(i)
Next i
End Sub
回答by HarveyFrench
You could avoid adding comments: Are you aware that users can add line breaks inside a cell by pressing ALT+RETURN?
您可以避免添加注释：您是否知道用户可以通过按 ALT+RETURN 在单元格内添加换行符？
If having high rows d is a problem and you don't like that formatting, an alternative approach might be to write a simple bit of code that changes the height of the current row when a user clicks in a certain range. It would , make other rows less high. Perhaps.
如果有高行 d 是个问题并且您不喜欢这种格式，另一种方法可能是编写一段简单的代码，当用户在某个范围内单击时更改当前行的高度。它会使其他行不那么高。也许。
Just a thought. It has benefits keeping it simple.
只是一个想法。它有保持简单的好处。
Harvey.
哈维。
